Comparing Dates In Two Columns using SQL Server

If you have to choose the most recent date kept in two columns, then here's the query to do so:

-- Create Sample Table
DECLARE @TT table
( ID int,
SomeDate1 datetime,
SomeDate2 datetime
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, '1/1/2009', '2/1/2009')
INSERT INTO @TT VALUES ( 2, '2/1/2009', '1/1/2009')
INSERT INTO @TT VALUES ( 3, '3/1/2009', '2/1/2009')
INSERT INTO @TT VALUES ( 4, '3/1/2009', '4/1/2009')
INSERT INTO @TT VALUES ( 5, '4/1/2009', '4/1/2009')
INSERT INTO @TT VALUES ( 6, '1/1/2009', '5/1/2009')
INSERT INTO @TT VALUES ( 7, '8/1/2009', '6/1/2009');
INSERT INTO @TT VALUES ( 8, '9/1/2009', '7/1/2009');
INSERT INTO @TT VALUES ( 9, '3/1/2009', '8/1/2009');
INSERT INTO @TT VALUES ( 10, '4/1/2009', '9/1/2009');


QUERY
SELECT ID,
CASE
WHEN SomeDate1 < SomeDate2
THEN SomeDate2
ELSE SomeDate1
END AS LastDate
FROM @TT

Results
ID LastDate
1 2009-02-01 00:00:00.000
2 2009-02-01 00:00:00.000
3 2009-03-01 00:00:00.000
4 2009-04-01 00:00:00.000
5 2009-04-01 00:00:00.000
6 2009-05-01 00:00:00.000
7 2009-08-01 00:00:00.000
8 2009-09-01 00:00:00.000
9 2009-08-01 00:00:00.000
10 2009-09-01 00:00:00.000

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook