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.

Answer 1

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

Answer 2

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