Selecting most recent date between two columns

Question

If I have a table that (among other columns) has two DATETIME columns, how would I select the most recent date from those two columns.

Example:

ID     Date1     Date2

1      1/1/2008   2/1/2008

2      2/1/2008   1/1/2008

3      1/10/2008  1/10/2008

If I wanted my results to look like

ID     MostRecentDate

1      2/1/2008

2      2/1/2008

3      1/10/2008

Is there a simple way of doing this that I am obviously overlooking? I know I can do subqueries and case statements or even write a function in sql server to handle it, but I had it in my head that there was a max-compare type function already built in that I am just forgetting about.

Solution 1

CASE is IMHO your best option:

SELECT ID,
       CASE WHEN Date1 > Date2 THEN Date1
            ELSE Date2
       END AS MostRecentDate
FROM Table

---

If one of the columns is nullable just need to enclose in COALESCE:

.. COALESCE(Date1, '1/1/1973') > COALESCE(Date2, '1/1/1973')

Solution 2

From SQL Server 2012 it's possible to use the shortcut IIF to CASE expression though the latter is SQL Standard:

SELECT ID,
       IIF(DateColA > DateColB, DateColA, DateColB) AS MostRecentDate
  FROM theTable

Solution 3

I think the accepted answer is the simplest. However, I would watch for null values in the dates...

SELECT ID,
       CASE WHEN ISNULL(Date1,'01-01-1753') > ISNULL(Date2,'01-01-1753') THEN Date1
            ELSE Date2
       END AS MostRecentDate
FROM Table

Solution 4

select ID, 
case
when Date1 > Date2 then Date1
else Date2
end as MostRecentDate
from MyTable

Solution 5

You can throw this into a scalar function, which makes handling nulls a little easier. Obviously it isn't going to be any faster than the inline case statement.

ALTER FUNCTION [fnGetMaxDateTime] (
    @dtDate1        DATETIME,
    @dtDate2        DATETIME
) RETURNS DATETIME AS
BEGIN
    DECLARE @dtReturn DATETIME;

    -- If either are NULL, then return NULL as cannot be determined.
    IF (@dtDate1 IS NULL) OR (@dtDate2 IS NULL)
        SET @dtReturn = NULL;

    IF (@dtDate1 > @dtDate2)
        SET @dtReturn = @dtDate1;
    ELSE
        SET @dtReturn = @dtDate2;

    RETURN @dtReturn;
END

View more solutions

Comments

[D'Arcy Rittich]

looks like we have a consensus :)

[TheTXI]

I figured that was the case. I was almost certain that there was a date comparison function already built where I could have just said "select ID, max(date1, date2)".

[TheTXI]

That's actually what I was going to end up doing for future use. I'm using the case method on this particular problem because of its simplicity, but the maximum date function is something I should have had

[bummi]

The question is about SQL-Server not Oracle.

[RyanNerd]

While this answer may be correct. Code only answers are rarely helpful. Please comment your code and provide an explanation for how this code solves the problem.

[Merin Nakarmi]

This is the best answer.

[Uttam]

Instead of giving a date such as '1/1/1973' we can give blank string '' which should get cast to the earliest date as per date type used.. e.g. CAST('' as date) retruns "1900-01-01", CAST('' as datetime) returns "1900-01-01 00:00:00.000" and CAST('' as datetime2) returns "1900-01-01 00:00:00.0000000". This works on SQL Server 2019.