CODEWITHSUNDEEP
sqlsql-servert-sqldate
Elio Fernandes
Elio Fernandes

Reputation: 1420

SQL comparing Null date with another date

I have this code and I would like to understand why it prints 'Equal' when comparing a null date with a non null date.

What should I do if I want to make an update and this happens?

Declare @OldDate date               
Declare @NewDate date = '05/02/1960'        

if(@OldDate <> @NewDate)
    Print 'Different'
else
    Print 'Equal'

Regards, Elio Fernandes

Upvotes: 0

Views: 491

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Almost any comparison to NULL returns NULL. In most contexts, NULL is treated as false. Hence, you get non-equal.

You don't specify your database. The standard supports NULL-safe comparisons:

@oldDate is distinct from @newDate

Not all databases support this syntax, so you need to be more explicit;

@oldDate <> @newDate or
@oldDate is null and @newDate is not null or
@oldDate is not null and @newDate is null

Upvotes: 3

Related Questions