在两列之间选择最近的日期

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.

如果我有一个表(在其他列中)有两个DATETIME列，我如何从这两列中选择最近的日期。

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.

有没有一种简单的方法可以让我忽略?我知道我可以进行子查询和case语句，甚至可以在sql server中编写一个函数来处理它，但是我在脑海中已经构建了一个max-compare类型函数，我只是忘记了它。

12 个解决方案

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

#2

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

#3

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.

您可以将它放入一个标量函数中，这使得处理nulls更加容易。显然，它的速度不会比内联的case语句快。

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

#4

I think the accepted answer is the simplest. However, I would watch for null values in the dates...

我认为公认的答案是最简单的。但是，我将注意日期中的空值……

SELECT ID,
       CASE WHEN ISNULL(Date1,'01-01-1753') > ISNULL(Date2,'01-01-1753') THEN Date1
            ELSE Date2
       END AS MostRecentDate
FROM Table

#5

Other than case statement, I don't believe so...

除了案例陈述，我不相信……

  Select Case When DateColA > DateColB Then DateColA 
              Else DateColB End MostRecent
  From Table ...

#6

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

尽可能地使用内联函数，因为它们不会遇到通常与udf相关的性能问题……

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

有关使用指南，请参见这里

#7

From SQL Server 2012 it's possible to use the shortcut IIF to CASE expression though the latter is SQL Standard:

从SQL Server 2012开始，可以使用快捷方式IIF来表示大小写，而后者是SQL标准:

SELECT ID,
       IIF(DateColA > DateColB, DateColA, DateColB) AS MostRecentDate
  FROM theTable

#8

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

AFAIK，没有内置函数可以得到两个值的最大值，但是您可以轻松地将自己的值写为:

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

#9

This thread has several solutions. If you had more than 2 dates to compare, "unpivot" might be preferable to writing a series of case statements. The following is blatantly stolen from Niikola:

这个线程有几个解决方案。如果要比较的日期超过2个，“unpivot”可能比编写一系列的case语句更可取。下面是公然从Niikola偷来的:

select id, max(dDate) MostRecentDate
  from YourTable
    unpivot (dDate for nDate in (Date1, Date2, Date3)) as u
  group by id

Then you can order by dDate, if that's helpful.

如果有帮助的话，你可以点dDate。

#10

All other correct answers as already posted.

所有其他正确答案已张贴。

But if you are still really looking for MAX keyword then here is a way :

但是如果你还在寻找MAX关键字，这里有一个方法:

select ID , MAX(dt) from 
(  select Id , Date1 as dt from table1
   union  
   select ID , Date2 from table2
) d
group by d.Id

#11

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

#12

-1

Why couldn't you use the GREATEST function?

为什么不能用最大函数呢?

select id, date1, date2, GREATEST( nvl(date1,date2) , nvl(date2, date1) )
from table1;

I included a NVL to ensure that NULL was evaluated correctly, otherwise if either Date1 or Date2 is null, the Greatest returns NULL.

我包含了一个NVL，以确保正确地评估NULL，否则如果Date1或Date2是NULL，最大的返回NULL。

ID  Date1       Date2       MostRecentDate
1   1/1/2008    2/1/2008    2/1/2008
2   2/1/2008    1/1/2008    2/1/2008
3   1/10/2008   1/10/2008   1/10/2008
4   -null-      2/10/2008   2/10/2008
5   2/10/2008   -null-      2/10/2008

#1