SQL Server - compare 3 date columns in a select query

Asked By muthuraman alexander on 19-Jan-12 12:21 AM
hi all
i have a select query in that i ll select 3 diff dates columns from the 3 diff tables using join
like
select bithdate ,joiningdate , createddate from table1 a
left outer join
table2 b
a.id=b.id

left outer join
table3 c
a.id=c.id

now i want to compare all these 3 dates and have to produce the latest date i.e) MAX date
how can i do this .....?.
please advice me

thanks in advance
MUTHU
D Company replied to muthuraman alexander on 19-Jan-12 12:37 AM
hello friend,

the simplest way to perform your task is, select all three value and put in 3 different variable let's say
// sample variable as bithdate ,joiningdate , createddate
Date1,Date2, Date3

than your query

SELECT 
    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 
        ELSE                
      Date1 
    END AS MostRecentDate 

hope this will help you!!

Regards
D
C D replied to muthuraman alexander on 19-Jan-12 02:02 AM
Hello

Try following way

select
CASE  WHEN a.bithdate > b.joiningdate and a.bithdate>c.createddate then a.bithdate
      WHEN b.joiningdate > a.bithdate and b.joiningdate>c.createddate then b.joiningdate
      When c.createddate > a.bithdate and c.createddate>b.joiningdate then c.createddate
      ELSE a.bithdate
End as MaxDate,
a.bithdate,b.joiningdate,c.createddate from table1 a
left outer join
table2 b on
a.id=b.id
left outer join
table3 c on
a.id=c.id


Hope this helpful
Sandeep Mittal replied to muthuraman alexander on 19-Jan-12 02:35 AM
SELECT  CASE WHEN joiningdate > bithdate
    THEN CASE WHEN createddate > joiningdate THEN createddate ELSE joiningdate END
    ELSE CASE WHEN createddate > bithdate THEN createddate ELSE bithdate END
    END AS MaxDate
FROM    table1 a
LEFT OUTER JOIN table2 b a.id=b.id
LEFT OUTER JOIN table3 c a.id=c.id
kalpana aparnathi replied to muthuraman alexander on 19-Jan-12 03:00 AM
hi,

Try this way:

 
WITH dates(date1, date2,date3) AS
(
SELECT CAST('2011-12-31 15:13:48.593' AS DATETIME),
     CAST('2011-12-31 00:00:00.000' AS DATETIME),
     CAST('2010-12-31 00:00:00.000' AS DATETIME)
)
SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) and CAST(date1 AS DATE) <= CAST(date3 AS DATE) or CAST(date2 AS DATE) <= CAST(date2 AS DATE) and CAST(date2 AS DATE) <= CAST(date3 AS DATE) or CAST(date3 AS DATE) <= CAST(date1 AS DATE) and CAST(date3 AS DATE) <= CAST(date2 AS DATE)
 THEN 'Y' ELSE 'N' END
 
FROM dates