CODEWITHSUNDEEP
sql-server
Kjartan Valur Þórðarson
Kjartan Valur Þórðarson

Reputation: 1209

Comparing dates if one date is NULL

Why do I get different result from these two samples?

DECLARE @validFrom DATE
DECLARE @validTo DATE

SET @validFrom = GETDATE()
SET @validTo = NULL

IF (@validFrom = @validTo)
   PRINT 'same'
ELSE
   PRINT 'not same'

Result: not same

But this code does not work:

DECLARE @validFrom DATE
DECLARE @validTo DATE

SET @validFrom = GETDATE()
SET @validTo = NULL

IF (@validFrom <> @validTo)
    PRINT 'not same'
ELSE
    PRINT 'same'

Result: same

Upvotes: 0

Views: 1300

Answers (2)

AlexK
AlexK

Reputation: 9947

DECLARE @validFrom DATE
DECLARE @validTo DATE

SET @validFrom = GETDATE()
SET @validTo = NULL

IF (@validFrom IS NULL AND @validTo IS NULL)
    OR (@validFrom = @validTo)
    PRINT 'same'
ELSE
    PRINT 'not same'

Izik Ben-Gan NULL complexities – Part 1

Upvotes: 1

marc_s
marc_s

Reputation: 755321

You CANNOT compare a NULL with the regular equality / inequality operators - this will always return NULL as result, which is being interpreted as "not defined" or "false". This is standard SQL behavior*.

Therefore the check

IF (@validFrom <> @validTo)

will always be false.

You can only check for NULL with the IS NULL or IS NOT NULL operators - but NOT with = or <> or < or <= or any of those standard operators.

More detail in the official documentation : Handling null values

Upvotes: 4

Related Questions