Menu
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.
231
Here is the syntax that we can use to get the latest date records in SQL Server. Select column_name, .. From table_name Order By date_column Desc; Now, let's use the given syntax to select the last 10 records from our sample table.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference.
SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';
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')
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
