T-SQL Better way to determine max of date (accounting for nulls)

Posted by Josh on Stack Overflow See other posts from Stack Overflow or by Josh
Published on 2010-05-20T13:56:25Z Indexed on 2010/05/20 14:00 UTC
Read the original article Hit count: 229

Filed under:

I am comparing two dates and trying to determine the max of the two dates. A null date would be considered less than a valid date. I am using the following case statement, which works - but feels very inefficient and clunky. Is there a better way?

update @TEMP_EARNED
set nextearn = case when lastoccurrence is null and lastearned is null then null
                   when lastoccurrence is null then lastearned
                   when lastearned is null then lastoccurrence
                   when lastoccurrence > lastearned then lastoccurrence
                   else lastearned end; 

(This is in MS SQL 2000, FYI.)

© Stack Overflow or respective owner

Related posts about tsql