check for null date in CASE statement, where have I gone wrong?

Question

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

Solution 1

Try:

select
     id,
     StartDate,
CASE WHEN StartDate IS NULL
    THEN 'Awaiting'
    ELSE 'Approved' END AS StartDateStatus
FROM myTable

You code would have been doing a When StartDate = NULL, I think.

---

NULL is never equal to NULL (as NULL is the absence of a value). NULL is also never not equal to NULL. The syntax noted above is ANSI SQL standard and the converse would be StartDate IS NOT NULL.

You can run the following:

SELECT CASE WHEN (NULL = NULL) THEN 1 ELSE 0 END AS EqualityCheck,
CASE WHEN (NULL <> NULL) THEN 1 ELSE 0 END AS InEqualityCheck,
CASE WHEN (NULL IS NULL) THEN 1 ELSE 0 END AS NullComparison

And this returns:

EqualityCheck = 0
InEqualityCheck = 0
NullComparison = 1

---

For completeness, in SQL Server you can:

SET ANSI_NULLS OFF;

Which would result in your equals comparisons working differently:

SET ANSI_NULLS OFF

SELECT CASE WHEN (NULL = NULL) THEN 1 ELSE 0 END AS EqualityCheck,
CASE WHEN (NULL <> NULL) THEN 1 ELSE 0 END AS InEqualityCheck,
CASE WHEN (NULL IS NULL) THEN 1 ELSE 0 END AS NullComparison

Which returns:

EqualityCheck = 1
InEqualityCheck = 0
NullComparison = 1

But I would highly recommend against doing this. People subsequently maintaining your code might be compelled to hunt you down and hurt you...

Also, it will no longer work in upcoming versions of SQL server:

https://msdn.microsoft.com/en-GB/library/ms188048.aspx

Solution 2

select Id, StartDate,
Case IsNull (StartDate , '01/01/1800')
When '01/01/1800' then
  'Awaiting'
Else
  'Approved'
END AS StartDateStatus
From MyTable

Comments

[Xordal]

If StartDate = '01/01/1800', result will be 'Awaiting'.

[Paul Brewczynski]

Why does the "ColumnName = NULL" doesn't work ? Why such a syntax isn't accepted ?

[Simon Hellings]

A very old comment - but the answer is because NULL is effectively unknown. If you write a simple query you will see that NULL <> NULL. select case when NULL = NULL then 1 else 0 END as IsNullEqual