Create a computed column on a datetime

Question

I have a nullable DateTime column in my SQL Server 2005 table called DateTimeDeleted.

I'd like to have a BIT type computed column that is 1 if DateTimeDeleted is not null, otherwise 0. However, I can't seem to get the syntax for the formula correct.

I've tried:

(TsDeleted IS NULL) = 0

but it gives me a syntax error.

Help! :)

Answer 1

This will work, basically it does a divide by itself if it's not null and then divide 0 by 1 if it's NULL

CONVERT(INT,IsNULL(DateTimeDeleted,0))/CONVERT(INT,IsNULL(DateTimeDeleted,1))

Answer 2

I think this should work:

update table
set IsDeleted = case when DateTimeDeleted is null then 0 else 1 end

Answer 3

Alter Table MyTableName 
  Add IsDeleted As 
     (Case When [DateTimeDeleted] Is Null 
       Then (0) Else (1) End)

This will output as an integer... If you really want it to be a bit, then:

Alter Table MyTableName 
  Add IsDeleted As 
     cast( (Case When [DateTimeDeleted] Is Null 
       Then (0) Else (1) End) as Bit)