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.

score 60 · Accepted Answer · edited Sep 05 '17 at 20:35

60

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

edited Sep 05 '17 at 20:35

Andre Figueiredo

12,930
8
48
74

answered Jan 05 '09 at 19:36

Rockcoder

8,289
3
32
41

5

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 Jan 05 '09 at 19:40
1

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. – Uttam Oct 04 '21 at 11:41

score 11 · Answer 2 · answered Sep 05 '17 at 20:40

11

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

answered Sep 05 '17 at 20:40

Andre Figueiredo

12,930
8
48
74

score 6 · Answer 3 · answered May 06 '15 at 19:44

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

score 5 · Answer 4 · edited Nov 28 '14 at 17:41

5

Why couldn't you use the GREATEST function?

select id, date1, date2, GREATEST( nvl(date1,date2) , nvl(date2, date1) )
from table1;

I included a NVL to ensure that NULL was evaluated correctly, otherwise if either Date1 or Date2 is null, the Greatest returns NULL.

ID  Date1       Date2       MostRecentDate
1   1/1/2008    2/1/2008    2/1/2008
2   2/1/2008    1/1/2008    2/1/2008
3   1/10/2008   1/10/2008   1/10/2008
4   -null-      2/10/2008   2/10/2008
5   2/10/2008   -null-      2/10/2008

edited Nov 28 '14 at 17:41

Rob Baillie

3,436
2
20
34

answered Nov 28 '14 at 16:54

matt

71
1
1

4

The question is about SQL-Server not Oracle. – bummi Nov 28 '14 at 17:07
3

On SQL 2022, it exists https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azure-sqldw-latest&viewFallbackFrom=sql-server-ver15 – roncansan Jul 18 '22 at 14:07
No need for the NVL function on SQL Server: "If one or more arguments are not NULL, then NULL arguments will be ignored during comparison. If all arguments are NULL, then GREATEST will return NULL." https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azure-sqldw-latest – TallArnie Sep 14 '22 at 09:19

score 5 · Answer 5 · answered Jan 05 '09 at 19:36

5

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

answered Jan 05 '09 at 19:36

D'Arcy Rittich

167,292
40
290
283

looks like we have a consensus :) – D'Arcy Rittich Jan 05 '09 at 19:38

score 5 · Answer 6 · answered Jan 05 '09 at 19:41

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

score 2 · Answer 7 · answered Jan 05 '09 at 19:36

2

Other than case statement, I don't believe so...

  Select Case When DateColA > DateColB Then DateColA 
              Else DateColB End MostRecent
  From Table ...

answered Jan 05 '09 at 19:36

Charles Bretana

143,358
22
150
216

score 2 · Answer 8 · answered Jan 05 '09 at 20:08

Whenever possible, use InLine functions as they suffer none of the performance issues generally associated with UDFs...

Create FUNCTION MaximumDate 
(   
@DateTime1 DateTime,
@DateTime2 DateTime
)
RETURNS TABLE 
AS
RETURN 
(
    Select Case When @DateTime1 > @DateTime2 Then @DateTime1
                Else @DateTime2 End MaxDate
)
GO

For usage guidelines, see Here

score 2 · Answer 9 · answered Jan 05 '09 at 20:11

2

AFAIK, there is no built-in function to get the maximum of two values, but you can write your own easily as:

CREATE FUNCTION dbo.GetMaximumDate(@date1 DATETIME, @date2 DATETIME)
RETURNS DATETIME
AS
BEGIN
    IF (@date1 > @date2)
        RETURN @date1
    RETURN @date2
END

and call it as

SELECT Id, dbo.GetMaximumDate(Date1, Date2)
FROM tableName

answered Jan 05 '09 at 20:11

Recep

18,991
2
28
21

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 – TheTXI Jan 06 '09 at 14:33
1

This is the best answer. – Merin Nakarmi Jun 19 '20 at 18:40

score 1 · Answer 10 · answered Apr 13 '12 at 03:15

1

All other correct answers as already posted.

But if you are still really looking for MAX keyword then here is a way :

select ID , MAX(dt) from 
(  select Id , Date1 as dt from table1
   union  
   select ID , Date2 from table2
) d
group by d.Id

answered Apr 13 '12 at 03:15

Dhananjay

3,673
2
22
20

score 1 · Answer 11 · answered Apr 23 '17 at 09:20

1

select max(d) ChangeDate
from (values(@d), (@d2)) as t(d)

answered Apr 23 '17 at 09:20

syb

369
2
5

Michael · Answer 12 · 2022-12-30T21:27:15.620

0

This question has several solutions. If you had more than 2 dates to compare, UNPIVOT might be preferable to writing a series of CASE statements. The following is blatantly stolen from Niikola:

SELECT  id, MAX(dDate) most_recent_date
FROM    your_table
UNPIVOT (d_date FOR n_date IN (date1, date2, date3)) AS u
GROUP BY id

Then you can ORDER BY d_date, if that's helpful.

edited Dec 30 '22 at 21:27

answered Apr 12 '12 at 18:06

Michael

8,362
6
61
88

score 0 · Answer 13 · answered Dec 25 '19 at 09:17

0

select ID,(select max(d) from (select Date1 d uninon select Date2 d) as t) as MaxDate
from MyTable

answered Dec 25 '19 at 09:17

Michal

9
6

4

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. – RyanNerd Dec 25 '19 at 11:45

Selecting most recent date between two columns

13 Answers13

Linked

Related