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

Answer 1

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