Testing for NULL in a CASE clause

Question

I'm confused as to why these two CASE statements provide different answers, when a DATETIME value is NULL.

DECLARE @MyDate DATETIME = NULL;
SELECT CASE ISNULL(@MyDate, 0)
    WHEN 0 THEN 'It Is Null'
    ELSE 'It Is a Date'
  END;

This results in the value 'It Is Null', which is straightforward - a NULL value is turned into 0, then we test for 0. However:

DECLARE @MyDate DATETIME = NULL;
SELECT CASE @MyDate
    WHEN NULL THEN 'It Is Null'
    ELSE 'It Is a Date'
  END;

The above results in 'It Is a Date', which is my point of confusion. The clause will not accept "WHEN IS NULL" which is what I first thought was the problem.

Why won't CASE WHEN recognise a NULL value?

Answer 1

The case expression syntax you are using is comparing the value you provided in the case part to each of the values provided and the when parts (and stops once it found a match). So what you are actually asking in that statement is @Date = NULL - that will always evaluate to false. You can write it like this:

SELECT CASE WHEN @MyDate IS NULL 
THEN  'It Is Null'
ELSE  'It Is a date'
END

Answer 2

Sorry found the answer after I posted, which is here:

T-SQL CASE Clause: How to specify WHEN NULL

The syntax is just a little different to achieve the right result.

Answer 3

I am not sure what RDBMS you are using but in T-SQL the correct (and the ANSI compliant overall) way to do it is:

DECLARE @MyDate DATETIME = NULL;
SELECT 
  CASE
    WHEN @MyDate IS NULL 
    THEN 'It Is Null'
    ELSE 'It Is a Date'
  END;

Answer 4

An observation I've made recently: people get really confused about CASE expressions, whereas the equivalent construct using UNION is easy to write and understand (and seems to handle nulls more intuitively!) e.g.

DECLARE @MyDate DATETIME = NULL;
SELECT 'It Is Null'
 WHERE @MyDate IS NULL
UNION
SELECT 'It Is a Date'
 WHERE @MyDate IS NOT NULL;