check for null date in CASE statement, where have I gone wrong?
- by James.Elsey
Hello,
My source table looks like this
Id StartDate
1 (null)
2 12/12/2009
3 10/10/2009
I want to create a select statement, that selects the above, but also has an additional column to display a varchar if the date is not null such as :
Id StartDate StartDateStatus
1 (null) Awaiting
2 12/12/2009 Approved
3 10/10/2009 Approved
I have the following in my select, but it doesn't seem to be working. All of the statuses are set to Approved even though the dates have some nulls
select
id,
StartDate,
CASE StartDate
WHEN null THEN 'Awaiting'
ELSE 'Approved' END AS StartDateStatus
FROM myTable
The results of my query look like :
Id StartDate StartDateStatus
1 (null) Approved
2 12/12/2009 Approved
3 10/10/2009 Approved
4 (null) Approved
5 (null) Approved
StartDate is a smalldatetime, is there some exception to how this should be treated?
Thanks
