Need the more recent between two DATETIME's in SQL

Question

I have two SQL DATETIME's that I need to compare. I need to set the value of a column to be the more recent date between the two. Is there a function for this?

Answer 1

You can use the DATEDIFF function to compare the dates.

DECLARE @Date1 DATETIME = '1/1/2010'
DECLARE @Date2 DATETIME = '2/1/2010'

SELECT CASE WHEN DATEDIFF(DD, @Date1, @Date2) < 0 THEN @Date1 ELSE @Date2 END

and UPDATE

UPDATE MyTable
SET MyDate = CASE WHEN DATEDIFF(DD, @Date1, @Date2) < 0 THEN Date1 ELSE Date2 END
WHERE <insert your conditions>

Assuming SQL Server.

Answer 2

Use a CASE statement.

UPDATE YourTable
    SET YourColumn = CASE WHEN @Date1 > @Date2 THEN @Date1 ELSE @Date2 END
    WHERE...

Answer 3

GREATEST would work for both numbers and dates.

update table set date_column = greatest(date1, date2); 

greatest(date1, date2) will return date2 if date2 is later than date1 and vice versa.

Answer 4

I need to set the value of a column to be the more recent date between the two.

I'd use the less than operator.

Yes, that's correct: the less than operator. No, not the greater than operator.

The reason is I find temporal data easier to read as a 'timeline' i.e. date values ascending in left-to-right order. Hence:

UPDATE YourTable
   SET your_col = CASE 
                      WHEN @Date1 < @Date2 THEN @Date2 
                      ELSE @Date1 
                   END
 WHERE ...

Answer 5

What I ended up doing was

CASE
    WHEN COALESCE(@Date1, '1/1/1980') > COALESCE(@Date2, '1/1/1980')
    THEN @Date1...
    ELSE @Date2...
    END     

I ended up doing this because if one of the dates was null, then the compare wouldn't work correctly. So I used COALESCE with a date in the past that I knew would be a good baseline, then did my comparison. Thanks for your help everyone!