A few months ago, I found myself working with a repetitive cursor that looped until the data had been manipulated enough times that it was finally correct. The cursor was heavily dependent upon dates, every time requiring the earlier of two (or several) dates in one stored procedure, while requiring the later of two dates in another stored procedure.
In short what I needed was a function that would allow me to perform the following evaluation:
WHERE MAX(Date1, Date2) < @SomeDate
The problem is, the MAX() function in SQL Server does not perform this functionality.
So, I set out to put these functions together. They are titled: EarlierOf() and LaterOf().
/**********************************************************
EarlierOf.sql
**********************************************************/
/**********************************************************
Return the later of two DATETIME variables.
Parameter 1: DATETIME1
Parameter 2: DATETIME2
Works for a variety of DATETIME or NULL values. Even
though comparisons with NULL are actually indeterminate, we
know conceptually that NULL is not earlier
or later than any other date provided.
SYNTAX:
SELECT dbo.EarlierOf('1/1/2000','12/1/2009')
SELECT dbo.EarlierOf('2009-12-01 00:00:00.000','2009-12-01 00:00:00.521')
SELECT dbo.EarlierOf('11/15/2000',NULL)
SELECT dbo.EarlierOf(NULL,'1/15/2004')
SELECT dbo.EarlierOf(NULL,NULL)
**********************************************************/
USE AdventureWorks
GO
IF EXISTS
(SELECT *
FROM sysobjects
WHERE name = 'EarlierOf'
AND xtype = 'FN'
)
BEGIN
DROP FUNCTION EarlierOf
END
GO
CREATE FUNCTION EarlierOf (
@Date1 DATETIME,
@Date2 DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ReturnDate DATETIME
IF (@Date1 IS NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = NULL
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NULL AND @Date2 IS NOT NULL)
BEGIN
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NOT NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = @Date1
GOTO EndOfFunction
END
ELSE
BEGIN
SET @ReturnDate = @Date1
IF @Date2 < @Date1
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
EndOfFunction:
RETURN @ReturnDate
END -- End Function
GO
---- Set Permissions
--GRANT SELECT ON EarlierOf TO UserRole1
--GRANT SELECT ON EarlierOf TO UserRole2
--GO
The inverse of this function is only slightly different.
/**********************************************************
LaterOf.sql
**********************************************************/
/**********************************************************
Return the later of two DATETIME variables.
Parameter 1: DATETIME1
Parameter 2: DATETIME2
Works for a variety of DATETIME or NULL values. Even
though comparisons with NULL are actually indeterminate, we
know conceptually that NULL is not earlier
or later than any other date provided.
SYNTAX:
SELECT dbo.LaterOf('1/1/2000','12/1/2009')
SELECT dbo.LaterOf('2009-12-01 00:00:00.000','2009-12-01 00:00:00.521')
SELECT dbo.LaterOf('11/15/2000',NULL)
SELECT dbo.LaterOf(NULL,'1/15/2004')
SELECT dbo.LaterOf(NULL,NULL)
**********************************************************/
USE AdventureWorks
GO
IF EXISTS
(SELECT *
FROM sysobjects
WHERE name = 'LaterOf'
AND xtype = 'FN'
)
BEGIN
DROP FUNCTION LaterOf
END
GO
CREATE FUNCTION LaterOf (
@Date1 DATETIME,
@Date2 DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ReturnDate DATETIME
IF (@Date1 IS NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = NULL
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NULL AND @Date2 IS NOT NULL)
BEGIN
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NOT NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = @Date1
GOTO EndOfFunction
END
ELSE
BEGIN
SET @ReturnDate = @Date1
IF @Date2 > @Date1
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
EndOfFunction:
RETURN @ReturnDate
END -- End Function
GO
---- Set Permissions
--GRANT SELECT ON LaterOf TO UserRole1
--GRANT SELECT ON LaterOf TO UserRole2
--GO
The interesting thing about this function is its simplicity and the built-in NULL handling functionality. Its interesting, because it seems like something should already exist in SQL Server that does this. From a different vantage point, if you create this functionality and it is easy to use (ideally, intuitively self-explanatory), you have made a successful contribution.
Interesting is good. Self-explanatory, or intuitive is FAR better. Happy coding!
Graeme
