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

User · Answer

select Id  StartDate  Case IsNull  StartDate    01 01 1800   When  01 01 1800  then    Awaiting  Else    Approved  END AS StartDateStatus From MyTable

User · Answer

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  lt  gt  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  lt  gt  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

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

Examples related to sql

Examples related to sql-server-2005

Examples related to case

Examples related to smalldatetime