Geeks With Blogs

News Locations of visitors to this page


Graeme Reisinger Welcome to my Office. My Other Office.

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 these function is their simplicity and the built-in NULL handling functionality.  They are interesting, because it seems like something should already exist in SQL Server to provide this functionality.  From a different vantage point, if you create this functionality and it is easy to use (ideally, intuitive or self-explanatory), you have made a successful contribution.

Interesting is good.  Self-explanatory, or intuitive is FAR better.  Happy coding!

Graeme

Posted on Sunday, June 13, 2010 1:56 PM SQL Server | Back to top


Comments on this post: SQL Server Date Comparison Functions

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © HighAltitudeCoder | Powered by: GeeksWithBlogs.net