CODEWITHSUNDEEP
sqlsql-server-2008datetimecompare
stacker
stacker

Reputation: 14931

sql - get the latest date of two columns

table1
    - date1 datetime not null
    - date2 nvarchar null

I want to get the latest date of this two.

select date1, date2, 
(CASE WHEN date1 > CAST(date2 as DateTime) THEN date1 ELSE date2 END) as DateTime) as LatestDate
from table1

please note that date2 can be null. in this case, date1 win.

Upvotes: 0

Views: 1524

Answers (3)

stacker
stacker

Reputation: 14931

This is very nice solution, but I suspect Nathan's solution is better.

http://p2p.wrox.com/oracle/6592-calculate-de-max-value-two-columns.html

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

change CAST(date2 as DateTime) to CAST(isnull(date2,'01/01/1900') as DateTime)

Upvotes: 1

Nathan Fisher
Nathan Fisher

Reputation: 7941

SELECT date1, date2,  
CASE 
  WHEN date1 > CAST(ISNULL(date2,'1900-01-01') as DateTime) THEN 
     date1 
  ELSE 
    CAST(date2 as DateTime) 
END  as LatestDate 
FROM table1

Upvotes: 1

Related Questions