How to copy values from bit column to datetime column?

Question

I want to copy values from a bit column called "absent" in table Person to a new, empty datetime column I just made.

The bit type column contains rows with values null, 0 and 1.

Now, I'd like to copy the values of this bit column to the new datetime column so, that all null AND 0 values in the bit column would be null values in the datetime column. All the 1 values would be new, current dates.

How to do that? I tried to search for w3bschools for some examples but couldn't find any.

Answer 1

Well, you can do it like this:

update Person
set dateTime_Column = case when isnull(absent, 0) = 0 then null else getdate() end

Answer 2

Try this:

update someTable
set dateColumn = case when bitColumn is null or bitColumn = 0 then null else getdate() end

Answer 3

You really just need to update the rows that have bitColumn = 1 and leave the others null if you didn't use a default value when you created the new column.

update Person
set dateTime_Column = getdate()
where bitColumn = 1