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

Try:

select     id,     StartDate,CASE WHEN StartDate IS NULL    THEN 'Awaiting'    ELSE 'Approved' END AS StartDateStatusFROM 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 = 0InEqualityCheck = 0NullComparison = 1

For completeness, in SQL Server you can:

SET ANSI_NULLS OFF;

Which would result in your equals comparisons working differently:

SET ANSI_NULLS OFFSELECT 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 = 1InEqualityCheck = 0NullComparison = 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

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