Elegant way to compare Dates in SQL Server with possible null values

Question

Is there a more elegant way to solve the problem of comparing dates in sql server where dates might be null? I don´t care about time.

DECLARE @validFrom DATE
DECLARE @validTo DATE
SET @validFrom = GETDATE()
SET @validTo = NULL
IF ((@validFrom is null AND @validTo IS NOT NULL) OR (@validTo is null AND @validFrom IS NOT NULL) OR DATEDIFF(day, @validFrom, @validTo) <> 0)
    PRINT 'not same'
ELSE
    PRINT 'same'

if both are NULL, what do you want ?

Squirrel
– Squirrel

2021-02-26 07:47:40 +00:00
Commented Feb 26, 2021 at 7:47 — Squirrel
– Squirrel, Commented Feb 26, 2021 at 7:47
Then I whant same

Kjartan Valur Þórðarson
– Kjartan Valur Þórðarson

2021-02-26 07:58:19 +00:00
Commented Feb 26, 2021 at 7:58 — Kjartan Valur Þórðarson
– Kjartan Valur Þórðarson, Commented Feb 26, 2021 at 7:58

Squirrel · Accepted Answer · 2021-02-26 08:02:58Z

2

You don't need to use DATEDIFF(DAY, ...) , you can compare the date directly since you have declared with DATE data type.

IF  @validFrom = @validTo
OR  (    -- when both are `NULL` consider same
        @validFrom  IS NULL 
    AND @validTo    IS NULL
    )
    PRINT 'same'
ELSE
    PRINT 'not same'

edited Feb 26, 2021 at 8:02

answered Feb 26, 2021 at 7:49

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jim · Accepted Answer · 2021-02-26 12:18:03Z

0

RE: a more elegant way to solve the problem of comparing dates in sql server where dates might be null?

Use ISNULL.

CODE:

SELECT CASE 
    WHEN ISNULL(@validFrom, '1900-01-01') = ISNULL(@validTo, '1900-01-01') 
    THEN 'same' 
    ELSE 'not same' END

answered Feb 26, 2021 at 12:18

jim

4565 silver badges13 bronze badges

2 Comments

Kjartan Valur Þórðarson Over a year ago

This will fail if one is null and other is 1900-01-01 ;) Unlikely but could happen

jim Over a year ago

Thank you for the comment. You are correct, I made the assumption that dates were business dates. Overwhelmingly, businesses do not have dates from 120 years ago. If you have a different range, one solution is to change default date to a date outside the range. The above is the most common way of handling NULLs in comparisons of any data type. It is easy to read and easy to type. BTW, the solution also does not address the business rule if both dates are NULL. I view programming as engineering, not math. There are no perfect answers. But there are many good ones.

Venkataraman R · Accepted Answer · 2021-02-26 12:41:22Z

0

You can check for nullability and then go for comparison.

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

IF ISNULL(@validFrom,'19000101') = ISNULL(@validTo,'19000101')
BEGIN
    PRINT 'same'
END
ELSE
BEGIN
    PRINT 'not same'
END

edited Feb 26, 2021 at 12:41

answered Feb 26, 2021 at 7:46

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

2 Comments

Kjartan Valur Þórðarson Over a year ago

Thanks, but really not any more elegant and if both are null it should be same...

Venkataraman R Over a year ago

@KjartanValurÞórðarson, got it. updated my answer.

Collectives™ on Stack Overflow

Elegant way to compare Dates in SQL Server with possible null values

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related