CODEWITHSUNDEEP
sql-serverdatecalculated-columns
Marco Eckstein
Marco Eckstein

Reputation: 4758

How to compare dates in computed columns?

DateColumn is of type date. I want to do this:

ALTER TABLE MyTable ADD MyComputedColumn AS (DateColumn < '2010-12-31');

I have also tried:

ALTER TABLE MyTable ADD MyComputedColumn AS (DateColumn < convert(date, '2010-12-31'));

But there are syntax errors. What is the correct syntax?

Upvotes: 1

Views: 1748

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6771

I'm guessing you want this to be a bit field:

ALTER TABLE MyTable ADD MyComputedColumn AS (ISNULL(CONVERT(bit,case when (DateColumn < '2010-12-31') 
then 0 
else 1 
end),0))

Upvotes: 1

jtimperley
jtimperley

Reputation: 2544

SQL Server doesn't have a data type to directly represent 'true' and 'false. You have to use a case statement and return a primitive type.

ALTER TABLE MyTable ADD MyComputedColumn AS (CASE WHEN (DateColumn < '2010-12-31') THEN 1 ELSE 0 END);

Upvotes: 4

Related Questions