Get the Latest date from the Three columns [duplicate]

Question

ID     Date1           Date2            Date3   
158    5/3/13 15:11    2/20/13 11:38    2/20/13 11:38

I want to get the latest date from this three columns.

Answer 1

SELECT CASE WHEN Date1 IS NOT NULL 
             AND Date1>=COALESCE(Date2,CAST('0001-01-01 00:00' AS DATETIME2)) 
             AND Date1>=COALESCE(Date3,CAST('0001-01-01 00:00' AS DATETIME2)) THEN Date1 
            WHEN Date2 IS NOT NULL 
             AND Date2>=COALESCE(Date1,CAST('0001-01-01 00:00' AS DATETIME2))
             AND Date2>=COALESCE(Date3,CAST('0001-01-01 00:00' AS DATETIME2)) THEN Date2 
            WHEN Date3 IS NOT NULL 
             AND Date3>=COALESCE(Date1,CAST('0001-01-01 00:00' AS DATETIME2))
             AND Date3>=COALESCE(Date2,CAST('0001-01-01 00:00' AS DATETIME2)) THEN Date3 
       END AS latest
FROM t1

Example

Answer 2

Try using CASE:

SELECT ID,
       CASE WHEN Date1>=Date2 AND  Date1>=Date3 THEN Date1
            WHEN Date2>=Date1 AND  Date2>=Date3 THEN Date2
            WHEN Date3>=Date1 AND  Date3>=Date2 THEN Date3
      END AS GreatestDate
FROM TableName

Answer 3

SELECT CASE WHEN date1 > date2 AND date1 > date3 THEN date1
            WHEN date2 > date3 THEN date2
            ELSE date3 END MaxDate
FROM TableName

Answer 4

Please try using UNPIVOT

SELECT MAX(MaxDt) MaxDt
   FROM tbl 
UNPIVOT
   (MaxDt FOR E IN 
      (Date1, Date2, Date3)
)AS unpvt;