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

Whenever possible, use InLine functions as they suffer none of the performance issues generally associated with UDFs...

Create FUNCTION MaximumDate 
(   
@DateTime1 DateTime,
@DateTime2 DateTime
)
RETURNS TABLE 
AS
RETURN 
(
    Select Case When @DateTime1 > @DateTime2 Then @DateTime1
                Else @DateTime2 End MaxDate
)
GO 

For usage guidelines, see Here

Answer 2

AFAIK, there is no built-in function to get the maximum of two values, but you can write your own easily as:

CREATE FUNCTION dbo.GetMaximumDate(@date1 DATETIME, @date2 DATETIME)
RETURNS DATETIME
AS
BEGIN
    IF (@date1 > @date2)
        RETURN @date1
    RETURN @date2
END

and call it as

SELECT Id, dbo.GetMaximumDate(Date1, Date2)
FROM tableName

Answer 3

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 4

select max(d) ChangeDate
from (values(@d), (@d2)) as t(d)

Answer 5

select ID, 
case
when Date1 > Date2 then Date1
else Date2
end as MostRecentDate
from MyTable

Answer 6

You can throw this into a scalar function, which makes handling nulls a little easier. Obviously it isn't going to be any faster than the inline case statement.

ALTER FUNCTION [fnGetMaxDateTime] (
    @dtDate1        DATETIME,
    @dtDate2        DATETIME
) RETURNS DATETIME AS
BEGIN
    DECLARE @dtReturn DATETIME;

    -- If either are NULL, then return NULL as cannot be determined.
    IF (@dtDate1 IS NULL) OR (@dtDate2 IS NULL)
        SET @dtReturn = NULL;

    IF (@dtDate1 > @dtDate2)
        SET @dtReturn = @dtDate1;
    ELSE
        SET @dtReturn = @dtDate2;

    RETURN @dtReturn;
END