How do you handle NULLs in a DATEDIFF comparison?

Question

I have to compare 2 separate columns to come up with the most recent date between them. I am using DATEDIFF(minute, date1, date2) to compare them, however, in some records the date is Null, which returns a null result and messes up the CASE.

Is there a way around this, or a way to predetermine which date is null up front?

(psudocode)

UPDATE TABLE
SET NAME = p.name,
    NEW_DATE = CASE WHEN DATEDIFF(minute,d.date1,d.date2) <= 0 THEN d.date
                    ELSE d.date2
               END
FROM TABLE2 d
INNER JOIN TABLE3 p
  ON d.ACCTNUM = p.ACCTNUM

Why use datediff instead of just comparing the two dates? Doesn't that just require extra processing time? — therealmitchconnors, May 09 '12 at 18:38
As you are new user on SO, you should accept an answer if you find it useful. Because it give answerer impression that they help you not wast of time. Some time new user ask a question and leave it. — Amit, May 09 '12 at 18:44

Andrey Gurinov · Answer 1 · 2012-05-09T18:44:42.077

3

You can just add extra logic into your CASE:

UPDATE TABLE 
SET NAME = p.name, 
    NEW_DATE = CASE 
                    WHEN d.date1 IS NULL THEN -- somewhat 
                    WHEN d.date2 IS NULL THEN -- somewhat 
                    WHEN DATEDIFF(minute,d.date1,d.date2) <= 0 THEN d.date 
                    ELSE d.date2 
               END 
FROM TABLE2 d 
INNER JOIN TABLE3 p 
  ON d.ACCTNUM = p.ACCTNUM

edited May 09 '12 at 18:44

answered May 09 '12 at 18:33

Andrey Gurinov

2,825
1
20
23

score 1 · Answer 2 · answered May 09 '12 at 18:35

I would use ISNULL.

UPDATE TABLE
SET NAME = p.name,
    NEW_DATE = CASE WHEN ISNULL(DATEDIFF(minute,d.date1,d.date2), 0) <= 0 THEN d.date
                    ELSE d.date2
               END
FROM TABLE2 d
INNER JOIN TABLE3 p
  ON d.ACCTNUM = p.ACCTNUM

or maybe

ISNULL(DATEDIFF(minute,d.date1,d.date2), 1)

if you want to handle the null values the other way around.

Amit · Answer 3 · 2012-05-09T18:40:36.507

You can try some think like this. You can use Is Null to check null.

UPDATE TABLE
SET NAME = p.name,
    NEW_DATE = CASE Case When date2 Is Null Then GetDate()
                    Case When date1 Is Null Then GetDate()
                    WHEN DATEDIFF(minute,d.date1,d.date2) <= 0 THEN d.date
                    ELSE d.date2
               END
FROM TABLE2 d
INNER JOIN TABLE3 p
  ON d.ACCTNUM = p.ACCTNUM

Microsoft's ISNULL() function is used to specify how we want to treat NULL values.

In this case we want NULL values to be zero.

Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:

SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products

SQL NULL Functions

therealmitchconnors · Answer 4 · 2012-05-09T18:43:06.820

0

This should give you what you want with minimal processing required.

UPDATE TABLE
SET NAME = p.name,
    NEW_DATE = CASE WHEN COALESCE(date1, date2)>COALESCE(date2, date1) 
                    THEN COALESCE(date1, date2) 
                    ELSE COALESCE(date2, date1)
               END
FROM TABLE2 d
INNER JOIN TABLE3 p
  ON d.ACCTNUM = p.ACCTNUM
WHERE NOT (date1 is null and date2 is null);

edited May 09 '12 at 18:43

answered May 09 '12 at 18:35

therealmitchconnors

2,732
1
18
36

Thank you all, it seems the NULL's have it. I wound up using DATEDIFF(MINUTE, ISNULL(date1, 0), ISNULL(date2, 0)) >= 1 THEN date2 ELSE date1 – user1385330 May 10 '12 at 18:34

score 0 · Answer 5 · answered Aug 29 '18 at 17:18

I personally use this:

IIF([yourvariable] is null, expression_when_true, expression_when_false)

This actually works well if you have more complex datatypes like DATE. I've looked for the solution also and lots of people asked how to substract null date or why simple 0000-00-00 doesn't work. The IIF can avoid substraction when minuend or subtrahend is null.

Example:

IIF([ReturnDate] is null,
datediff(day,[BookDate],getdate())*CostPerDay,  
datediff(day,[BookDate],[ReturnDate])*CostPerDay)

How do you handle NULLs in a DATEDIFF comparison?

5 Answers5