Sql – Create a computed column on a datetime

calculated-columnssql-server-2005

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! 🙂

Best Solution

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)