I have a table with several date fields. I need to get a derived field that is the oldest date of 3 particular fields. I have to do this for 4 different date sets.
Add a comment
|
1 Answer
One simple way is to use a case expressions:
select case when Date1 > Date2 and Date1 > Date3 then
Date1
when Date2 > Date3 and Date2 > Date1 then
Date2
else
Date3
end As TheDate
from tableName
Though this is quite simple, it tends to get really cumbersome really fast, if you have more columns. Another option would be to use the values clause, like this:
select (
select max(date)
from (values (date1), (date2), (date3))v(date)
)
from tableName
This also enables you to get min value easily.
10 Comments
Juan Carlos Oropeza
You have to add something on the ELSE for Date 2 vs Date 3
Zohar Peled
@JuanCarlosOropeza corrected.
Juan Carlos Oropeza
Well you change it, did you saw a problem with the previous one?
Zohar Peled
shorton
I had some trouble with the syntax btu got it to work from an example in the other thread marked as a dup. SELECT [Other Fields], (SELECT Max(v) FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate] FROM [YourTableName]
|