In SQL Server, how do I get the latest dates with multiple columns of dates?

Question

This feels like it should be an easy one. How do I get the latest of 3 dates that are in different columns

DROP TABLE #dates
CREATE TABLE #dates (mykey CHAR(10), date1 DATETIME, date2 DATETIME, date3 DATETIME)
INSERT #dates VALUES ('Key1', '1/1/2015', '2/1/2015', '3/1/2105')
INSERT #dates VALUES ('Key2', '1/2/2015', '4/2/2015', '3/2/2105')
INSERT #dates VALUES ('Key3', '1/3/2016', '4/3/2015', '3/3/2105')

select mykey, ?? AS 'Latest Date' from #dates

I would like the result to be:

mykey       Latest Date
Key1        2105-03-01 00:00:00.000
Key2        2015-04-02 00:00:00.000
Key3        2016-01-03 00:00:00.000

You can easily achieve it using MAX from derived table based on UNION ALL/VALUES multiple columns — Lukasz Szozda
– Lukasz Szozda, Commented Dec 21, 2015 at 21:43
I re-opened the question because I think the below answer is better than the referenced duplicate question. — Gordon Linoff
– Gordon Linoff, Commented Dec 21, 2015 at 21:43
date3 should be in 2015 as well, not in 2105 in order to get the results you wanted, otherwise it will return date3 for all of the keys — MazBros
– MazBros, Commented Dec 21, 2015 at 23:03
Ideally, fix your data model. All data of the same "type" (such that it makes sense that two values will be compared to each other) ought to be in a single column. When you have numbered columns, it's usually a sign that data has ended up embedded in metadata (i.e. 1, 2 and 3 are meaningful, but there's no way to write straightforward queries that use those numbers) or you have arbitrary restrictions (why does it stop at 3?) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 22, 2015 at 6:59

Gordon Linoff · Accepted Answer · 2015-12-21 22:21:48Z

8

Probably the easiest way in SQL Server is to use cross apply:

select d.*, m.maxdate
from #dates d cross apply
     (select max(dte) as maxdate
      from (values (date1), (date2), (date3)) as v(dte)
     )  m;

edited Dec 21, 2015 at 22:21

answered Dec 21, 2015 at 21:44

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mohan111 Over a year ago

even though it is good query it is not giving exact result as mentioned in output

Gordon Linoff Over a year ago

@mohan111 . . . Presumably, the 2105 year is a typo in the sample data, based on the context of the question. Here is a SQL Fiddle: sqlfiddle.com/#!6/a52e6/2.

mohan111 Over a year ago

No Exactly we need to get 2016 for key3 but In your query it is showing other value we need to MAX means highest date

Gordon Linoff Over a year ago

@mohan111 . . . If I change the years in the third column, it returns 2016: sqlfiddle.com/#!6/09613/1.

Steven · Accepted Answer · 2015-12-21 22:37:49Z

Using a CASE statement would get the job done.

DECLARE @dates TABLE (mykey CHAR(10), date1 DATETIME, date2 DATETIME, date3 DATETIME)
INSERT @dates VALUES ('Key1', '1/1/2015', '2/1/2015', '3/1/2105')
INSERT @dates  VALUES ('Key2', '1/2/2015', '4/2/2015', '3/2/2105')
INSERT @dates  VALUES ('Key3', '1/3/2016', '4/3/2015', '3/3/2105')

select mykey, 
case when date1 >= date2 and date1 >= date3 THEN date1
    when date2 >= date1 and date2 >= date3 then date2
    else date3 end [LatestDate]
 from @dates

slavoo · Accepted Answer · 2015-12-22 06:59:00Z

1

Here we can use iif statement:

SELECT mykey
    ,iif(month(date1) > month(date2), iif(month(date1) > month(date3), date1, date3), iif(month(date2) > month(date3), date2, date3)) AS result
FROM #dates

edited Dec 22, 2015 at 6:59

slavoo

6,11464 gold badges40 silver badges41 bronze badges

answered Dec 22, 2015 at 6:31

Chanukya

5,9011 gold badge24 silver badges39 bronze badges

Collectives™ on Stack Overflow

In SQL Server, how do I get the latest dates with multiple columns of dates?

3 Answers 3

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Linked

Related