You are right. I was trying to cast to a Date, which did not work, but DateTime works perfectly!

You will also need to do an isNull check on the 2nd When of the case statement. You can verify this by using the expanded data set below:

declare @t1 table

(

a date

,b date

,c date

,d date

)

insert @t1 (a,b,c,d) values

(null, getdate(), getdate()+1, getdate()+2),

(getdate()+2, null, getdate(), null),

(getdate()+3, getdate()+1, null, getdate()),

(getdate(), getdate()+2, getdate(), null),

(GETDATE(), null, null, null),

(null, GETDATE(), null, null),

(null, null, getdate(), null),

(null, null, null, null)

SELECT a, b, c, CASE WHEN a >= ISNULL(b, CAST( 0 AS DATETIME)) AND a >= ISNULL(c, CAST( 0 AS DATETIME)) THEN a

WHEN b >= isnull(c, CAST(0 as DATETIME)) THEN b

ELSE c END MaxDate

FROM @t1