Binaryworld - Minimum value from 4 variables - datetime example. ... [ SQL Server -> T-SQL ]

This scripts shows how to obtain minimum value of four 4 datatime values contained in local variables. This method can be adapted to any datatye supported by the the SQL Server MIN function. This technique basically eliminates the writing of series of IF..ELSE or CASE statements.

Click here to copy the following block

DECLARE @d1 datetime , @d2 datetime , @d3 datetime , @d4 datetime ,
    @MinDate datetime
SELECT @d1 = DATEADD( dd, ( RAND()*10 + 1 ) , CURRENT_TIMESTAMP ) ,
    @d2 = DATEADD( dd, ( RAND()*10 + 1 ) , CURRENT_TIMESTAMP ) ,
    @d3 = DATEADD( dd, ( RAND()*10 + 1 ) , CURRENT_TIMESTAMP ) ,
    @d4 = DATEADD( dd, ( RAND()*10 + 1 ) , CURRENT_TIMESTAMP )
PRINT 'Date #1: ' + CAST( @d1 AS varchar )
PRINT 'Date #2: ' + CAST( @d2 AS varchar )
PRINT 'Date #3: ' + CAST( @d3 AS varchar )
PRINT 'Date #4: ' + CAST( @d4 AS varchar )
/*
Date #1: Sep 8 2000 7:31PM
Date #2: Sep 4 2000 7:31PM
Date #3: Aug 31 2000 7:31PM
Date #4: Sep 2 2000 7:31PM
*/
SELECT @MinDate = MIN( dateval )
FROM (
SELECT @d1 AS dateval
UNION ALL
SELECT @d2
UNION ALL
SELECT @d3
UNION ALL
SELECT @d4
) AS d
PRINT 'Min. Date: ' + CAST( @MinDate AS varchar )
/*
Min. Date: Aug 31 2000 7:31PM
*/