get the valid date of the 3 columns

Question

environment: microsoft sql server 2005, 2008

given:

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
   DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
   ID int IDENTITY(1,1)
   , date1 datetime
   , date2 datetime
   , date3 datetime
)
INSERT INTO #MyTempTable
SELECT '3/1/2012','3/2/2012',NULL

table datetest
id,date1,date2,date3
------------------------
1, 3/1/2012,3/2/2012,null
2, 3/1/2012,null,null

all 3 dates can be null and up to 3 dates can be entered.

how do you get the most recent date?

for example, if date1 and date2 were filled out for a row, how would you get the most recent date of the 2 columns for that row?

Use a nested case statement to compare the dates with ifnull setting them to one less than the other date. A good example is at http://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server — Chetter Hummin, Mar 22 '12 at 18:04

score 4 · Accepted Answer · answered Mar 22 '12 at 18:07

4

This works for SQL server 2008

DECLARE @MyTempTable
 TABLE (
   ID int IDENTITY(1,1)
   , date1 datetime
   , date2 datetime
   , date3 datetime
)

INSERT INTO @MyTempTable 
SELECT '3/1/2012','3/2/2012',NULL

SELECT [id], (SELECT Max(Dt) FROM (VALUES (date1),(date2),(date3)) AS value(Dt)) as [RecentDate]
FROM @MyTempTable

answered Mar 22 '12 at 18:07

rs.

26,707
12
68
90

Wow! That's Great! Up from me – Andrey Gurinov Mar 22 '12 at 18:10

score 1 · Answer 2 · answered Mar 22 '12 at 18:24

had this problem recently. Was trying to find an equivalent to the GREATEST function in oracle which would do this in a pinch, but found there was no equivalent in SQL Server.

Found a nice solution using pivot for this type of problem http://social.msdn.microsoft.com/Forums/is/transactsql/thread/d5583d1e-df78-4552-80e9-c63cfe0eb0c3

SELECT id, max(dt) as max_date
FROM #MyTempTable
unpivot(dt FOR columnname in ([date1],[date2],[date3])) AS pv
GROUP BY id

this solution works for both Sql Server 2005 and Sql Server 2008 — rlobban, Mar 23 '12 at 17:40

score 0 · Answer 3 · answered Mar 22 '12 at 18:07

You could normalize the table using a subquery:

select  id
,       max(date_column)
from    (
        select  id
        ,       date1 as date_column
        from    @MyTempTable
        union all
        select  id
        ,       date2
        from    @MyTempTable
        union all
        select  id
        ,       date3
        from    @MyTempTable
        ) as NormalizedTable

(For production, use Yuck's answer :-))

score 0 · Answer 4 · answered Mar 22 '12 at 18:07

0

For SQL Server 2008 you can do the following:

SELECT ID, MIN(dates) MinDate, MAX(dates) MaxDate
FROM #MyTempTable A
CROSS APPLY (SELECT * FROM (VALUES  (date1),
                                    (date2)) VAL(dates)) B
GROUP BY ID

answered Mar 22 '12 at 18:07

Lamak

69,480
12
108
116

score 0 · Answer 5 · answered Mar 22 '12 at 18:08

Try this:

; WITH CTE AS (
    SELECT  
        ID,
        ISNULL(date1, CAST(0 AS DATETIME)) AS date1,
        ISNULL(date2, CAST(0 AS DATETIME)) AS date2,
        ISNULL(date3, CAST(0 AS DATETIME)) AS date3
    FROM #MyTempTable
)
SELECT 
    ID,
    NULLIF(
        CASE 
            WHEN date1 > date2 THEN 
                CASE WHEN date1 > date3 THEN date1 ELSE date3 END
            ELSE 
                CASE WHEN date2 > date3 THEN date2 ELSE date3 END
        END,
        CAST(0 AS DATETIME))

FROM CTE

get the valid date of the 3 columns

5 Answers5