SQL Server select min date from 3 columns

Question

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.

Answer 1

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.