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

Question

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.)

Answer 1

The earliest date SQL Server can store in a datetime field is 1st Jan 1753, so if you just count nulls as that date then you can do it in a single line:

set nextearn = case when coalesce(lastoccurrence, '1753-01-01 00:00') > coalesce(lastearned, '1753-01-01 00:00') then lastoccurrence else lastearned end;

If your field is a smalldatetime then the minimum is 1st Jan 1900 so it would be:

set nextearn = case when coalesce(lastoccurrence, '1900-01-01 00:00') > coalesce(lastearned, '1900-01-01 00:00') then lastoccurrence else lastearned end;

Answer 2

Try this:

Set nextearn = Case When IsNull(lastoccurrence, 0) > IsNull(lastearned , 0) 
               Then lastoccurrence Else lastearned  End

All dates in sql server are stored as two integers, opne that represents the date (number of days since 1 jan 1900) and thh other that represents the time (number of seconds [SmallDatetime] or milleseconds[DateTime] since midnight)...

The two numbers are combined as a Integer.DecimalFraction (DateInteger.TimePortion). So the number 0.0 represents Midnight 1 Jan 1900, 2.5 represents noon on 3 Jan 1900, etc...

Using the number in your expression eliminates the need to parse the string representation into a number before comparing it with the number which represents the other date...

Answer 3

select c1, c2, 
       case when c1 > c2 then c1 else coalesce(c2,c1) end as max 
from twocol;
+------+------+------+
| c1   | c2   | max  |
+------+------+------+
| NULL | NULL | NULL |
| NULL |    2 |    2 |
|    1 | NULL |    1 |
|    1 |    2 |    2 |
| NULL | NULL | NULL |
|    2 | NULL |    2 |
| NULL |    1 |    1 |
|    2 |    1 |    2 |
+------+------+------+

Why does this work? if neither operand is null, then we get a "normal" comparison: the "then" branch when c1 > c2, the else branch when c1 <= c2.

On the "else" branch, we call coalesce, but as its first argument is the non-null c2, we return c2.

.

But if either operand is null, the test c1 > c2 evaluates to false, and we return coalesce( c2, c1 ).

If the null operand was c1, we get c2, which is what we want because we are (for this question) calling null is "less than" any non-null value.

If the null operand was c2, we get c1. That's fine, because c1 is either not null, and thus (for this question) "greater than" the null c2, or...

If both operands were null, we get c1, but it doesn't matter which we get, as both are null.

An additional advantage is that this idiom works for any types for which operator > works, without further thought or details.

Answer 4

Use GREATEST:

update @TEMP_EARNED 
set nextearn = GREATEST(lastoccurrence,lastearned)

[EDIT]

Since there is no built-in GREATEST function in SQL Server, we must create one:

create function dbo.GREATEST(@exp1 SQL_VARIANT, @exp2 SQL_VARIANT)
returns SQL_VARIANT
as
begin
    return 
    (
        select max(v)
        from
        (
            select @exp1 as v
            union 
            select @exp2
        )x
    );
end;

To use:

update @TEMP_EARNED 
set nextearn = dbo.GREATEST(lastoccurrence,lastearned)

That approach will work even with three(or more columns), just create another function(would be a lot easier if Sql Server has variadic function):

create function dbo.GREATEST3(@exp1 SQL_VARIANT, @exp2 SQL_VARIANT, @exp3 SQL_VARIANT)
returns SQL_VARIANT
as
begin
    return 
    (
        select max(v)
        from
        (
            select @exp1 as v
            union 
            select @exp2
            union
            select @exp3
        )x
    );
end;

To use:

select dbo.GREATEST3(6,null,7);