Why does comparing a SQL date variable to null behave in this way?

Question

I just came across an interesting problem with a procedure I am writing in SQL.

In my proc I have 2 dates, which are optional params defaulted to NULL, I want to check if these params are not null and if not run part of my proc, if they are null then the extra part of the proc is ignored.

I did a fairly basic IF(@dateVariable <> NULL AND @DateVariable2 <> NULL) statement, but the if statement never works even if the variables are not null, I would assume SQL is struggling to compare the date to a NULL which is strange since datetime is nullable.

To get around this I just did IF(DateVariable IS NOT NULL) which works correctly. I also tried IF( ISNULL(@DateVariable,'') <> '') which also works correctly

So my question is why does the first IF not work, but the second and third IF both do since both must at some point compare the contents of the variable to null?

Example:

----- Fails -----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date <> NULL)
BEGIN
    print('a')
END

----- Works -----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (ISNULL(@Date,'') <> '')
BEGIN
    print('a')
END

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date IS NOT NULL)
BEGIN
    print('a')
END

Answer 1

Simply put 'NULL' does not equal 'NULL'. 'NULL' is comparable to a state of uncertainty, where one thing being uncertain does not necessarily equal something else that is also uncertain. Use 'IS NULL', 'ISNULL()', or 'COALESCE()' when testing for nulls. Setting ANSI_NULLS to 'off' can change this behavior, but it is not the ANSI SQL standard. See http://msdn.microsoft.com/en-us/library/ms191270.aspx for more info.

Answer 2

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL.

See NULL Comparison Search Conditions