Creating A Boolean Computed Column

Question

Consider the following columns:

 

EmpID, Status, TermDate

 

I want a computed column called OkToDisable as a bit value:  True if the TermDate is any day before today.

 

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

 

I cant figure out how to do this...  SQL keeps complaining that it can't validate the formula.

 

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to:  'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

 

Can't you create boolean computed fields? 

 

Thanks.

 

J

Answer 1

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com

Answer 2

Bit is a boolean type, no?  )

 

I had tried the CASE but I couldn't get it to work properly...  It helps when you use the proper syntax.  (I had left off the END statement)

 

Then I had to use a cast, otherwise it was an integer.

 

Thanks for the reply.

 

J

Answer 3

When I use a case statement as above, the data type of the result is an int not a bit. 
Ah, a little more Googling and the solution is to use cast(1 as bit) and cast(0 as bit).