How do you handle NULL in a DATEDIFF comparison?

Question

I need to compare two separate columns to find the latest date between them. I use DATEDIFF (minute, date1, date2) to compare them, however in some entries the date is Null, which returns a null result and ruins the CASE.

Is there a way around this or a way to predefine which date is zero in 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 add additional logic to 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 

Answer 2

You can try to think like this. You can use Is Null to check for a null value.

 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 

The Microsoft ISNULL () function is used to indicate how we want to treat NULL values.

In this case, we want the NULL values ​​to be zero.

Below, if "UnitsOnOrder" is NULL, this will not hurt the calculation, because ISNULL () returns zero if the value is NULL:

SQL Server / MS Access

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

SQL NULL Functions

Answer 3

This should give you what you want, with minimal processing needed.

 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); 

Answer 4

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 null values ​​in a different way.