Better Min/Max Function for Dates in T-SQL

Question

I created the following function to determine the MAX date between two dates. It takes roughly from 00.030 to 00.050 seconds to run one of the SELECT statements in the function's comment.

Is there a better performing and cleaner way?

/* Returns the greater of two dates.

    SELECT dbo.fnMaxDate(NULL      , NULL)
    SELECT dbo.fnMaxDate('1/1/2011', NULL)
    SELECT dbo.fnMaxDate(NULL      , '1/1/2011')
    SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011')
    SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011')
    SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011')

*/
ALTER FUNCTION dbo.fnMaxDate 
(
    @Date1 DATETIME,
    @Date2 DATETIME
)
RETURNS datetime
AS
BEGIN

    DECLARE @Result DATETIME

    IF @Date1 IS NULL AND @Date2 IS NULL

        SET @Result = NULL;

    ELSE IF @Date1 IS NULL

        SET @Result = @Date2

    ELSE IF @Date2 IS NULL

        SET @Result = @Date1

    ELSE
        IF @Date1 >= @Date2

            SET @Result = @Date1

        ELSE

            SET @Result = @Date2

    RETURN @Result

END

Answer 1

ALTER FUNCTION dbo.fnMaxDate 
(
    @Date1 DATETIME,
    @Date2 DATETIME
)
RETURNS datetime
AS
BEGIN

    RETURN
        CASE
            WHEN ISNULL(@Date1, @Date2) > ISNULL(@Date2, @Date1)
            THEN ISNULL(@Date1, @Date2)
            ELSE ISNULL(@Date2, @Date1)
        END
END

Answer 2

I found that a case structure runs about three times faster than a function call.

declare @d table(d1 date, d2 date);
insert into @d values(null,null)
, ('2/19/2012',null)
, (null,'2/19/2012')
, ('2/1/2012','2/15/2012')
, ('2/1/2012','1/15/2012');


declare @md date
, @i int=1
, @ts datetime2
, @ms1 int
, @ms2 int;

-- function
select @ts=GETDATE();
while @i<10000 begin select @md=dbo.fnMaxDate(d1,d2) from @d; set @i+=1; end
select @ms1=DATEDIFF(ms,@ts,GETDATE());

-- recommended case structure
set @i=1;
select @ts=GETDATE();
while @i<10000 begin select @md=case when ISNULL(d1,d2)<ISNULL(d2,d1) then ISNULL(d2,d1) else ISNULL(d1,d2) end from @d; set @i+=1; end
select @ms2=DATEDIFF(ms,@ts,GETDATE());
select [Function Run Time (ms)]=@ms1, [Case Run Time (ms)]=@ms2
go

Result:

Function Run Time (ms) Case Run Time (ms)
---------------------- ------------------
940                    296

Answer 3

    /* Returns the greater of two dates.

        SELECT dbo.fnMaxDate(NULL      , NULL)
        SELECT dbo.fnMaxDate('1/1/2011', NULL)
        SELECT dbo.fnMaxDate(NULL      , '1/1/2011')
        SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011')
        SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011')
        SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011')

    */
ALTER FUNCTION dbo.fnMaxDate 
(
    @Date1 DATETIME,
    @Date2 DATETIME
)
RETURNS datetime
AS
BEGIN

   DECLARE @Result datetime

   SELECT TOP 1 @Result =  T.DateValue
   FROM
     (
     SELECT @Date1 DateValue
     UNION ALL
     SELECT @Date2 DateValue
     ) T
   ORDER BY
     T.DateValue DESC

    RETURN @Result

END

Answer 4

I know this is an old question, and already answered, but seeing as there was already a test script I couldn't resist having a go :)

I've created a scalar function that outperforms all the other function tests here, and a table valued function that is even faster than the scalar UDF.

Comparable Result (to AMissico's test)

TVF = 0.022253313291
SVF = 0.04627526226

The Functions

/* Returns the greater of two dates.

    SELECT dbo.fnMaxDate(NULL      , NULL)
    SELECT dbo.fnMaxDate('1/1/2011', NULL)
    SELECT dbo.fnMaxDate(NULL      , '1/1/2011')
    SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011')
    SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011')
    SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011')

*/
CREATE FUNCTION dbo.svfMaxDate 
(
    @Date1 DATETIME,
    @Date2 DATETIME
)
RETURNS datetime
AS
BEGIN

    RETURN coalesce(dateadd(dd, ((datediff(dd, 0, @Date1) + datediff(dd, 0, @Date2)) + abs(datediff(dd, 0, @Date1) - datediff(dd, 0, @Date2))) / 2, 0), @Date1, @Date2)

END
GO

CREATE FUNCTION dbo.tvfMaxDate
(
    @Date1 DATETIME,
    @Date2 DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS

    RETURN SELECT [MaxDate] = coalesce(dateadd(dd, ((datediff(dd, 0, @Date1) + datediff(dd, 0, @Date2)) + abs(datediff(dd, 0, @Date1) - datediff(dd, 0, @Date2))) / 2, 0), @Date1, @Date2)
    ;
GO

I also used AMissico's simple test script to test this, but only bothered to test my version of the functions and the CASE statement to be used as a reference point/baseline:

Results of Test

Case(ms)    svfMaxDate  tvfMaxDate
0.01343000  0.03907000  0.01837000

Normalise Test Results to AMissico's Baseline

Baseline Case(ms) / This Test Case(ms) = Normalisation Factor
0.01616 / 0.01334000 = 1.21139430

svfMaxDate * Normalisation Factor= Comparable Result
0.03820000 * 1.21139430 = 0.04627526226

tvfMaxDate * Normalisation Factor= Comparable Result
0.01837 * 1.21139430 = 0.022253313291

Test Script

declare @d table(d1 date, d2 date); 
insert into @d values(null,null) 
, ('2/19/2012',null) 
, (null,'2/19/2012') 
, ('2/1/2012','2/15/2012') 
, ('2/1/2012','1/15/2012'); 


declare @md date 
, @i int=1 
, @ts datetime2 
, @ms0 int 
, @ms1 int 
, @ms2 int 
;

-- case structure 
set @i=1; 
select @ts=GETDATE(); 
while @i<100000 begin select @md=case when ISNULL(d1,d2)<ISNULL(d2,d1) then ISNULL(d2,d1) else ISNULL(d1,d2) end from @d; set @i+=1; end 
select @ms0=DATEDIFF(ms,@ts,GETDATE()); 

-- svfMaxDate, Arithmetic
set @i=1; 
select @ts=GETDATE(); 
while @i<100000 begin select @md=dbo.svfMaxDate(d1,d2) from @d; set @i+=1; end 
select @ms1=DATEDIFF(ms,@ts,GETDATE()); 

-- tvfMaxDate, Arithmetic in TVF with CROSS APPLY
set @i=1; 
select @ts=GETDATE(); 
while @i<100000 begin select @md = tf.MaxDate from @d cross apply dbo.tvfMaxDate(d1,d2) as tf; set @i+=1; end 
select @ms2=DATEDIFF(ms,@ts,GETDATE()); 

select [Case(ms)]=@ms0/100000.0, fnMaxDate=@ms1/100000.0, tvfMaxDate=@ms2/100000.0
go 

The TVF credit goes to Jeff Moden, the following link was used as a reference for building it: How to Make Scalar UDFs Run Faster (SQL Spackle)