All geek questions in one place

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

It seems like it should be easy. How to get the last of three 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 to get the result:

 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
+5
source share
3 answers

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;
+8
source

Using the CASE statement, you get the job.

 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
0
source

Here we can use the iif operator:

 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
0
source

Source: https://habr.com/ru/post/1238889/

More articles:


All Articles