Date Range Check in SQL Server

I have been writing a number of stored procedures that return report data for a client. Each of these reports can be filtered by a date range, so I needed a simple way to check that a date was within a user defined date range.

As an additional requirement, if the user does not specify a begin date or end date as part of the date range then the earliest or latest supported date respectively should be used. In other words, if the user does not specify a begin date then the earliest date supported by SQL Server should be used as the begin date, and if the user does not specify the end date then the latest date supported is used for the end date.

My original approach was to use a BETWEEN clause in the SELECT statements. However, this made the code messy especially with the ISNULL call used to set the begin end dates when NULL. So I swapped out the code with a user-defined function.

The user-defined function fnIsInDateRange takes 3 parameters:

Date to check
Beginning date of the date range
Ending date of the date range

The function returns one of the following:

-1 if the date is before the range
0 if the date is within the range
1 if the date is beyond the range

Here's the code for those who want it.


CREATE FUNCTION dbo.fnIsInDateRange
/* 
  Determines if the date time value is within the date time range.
  
  Returns:
  -1 if the date is before the date range.
   0 if the date is within the date range.
   1 if the date is after the date range.
   
  Usage:
   SELECT dbo.fnIsInDateRange('2005-08-16','2005-08-01','2005-08-15')
   SELECT * FROM exams WHERE dbo.fnIsInDateRange(dtmExamDate,'2005-08-01','2005-08-15')=0
*/  
  (
    @value      DATETIME,
    @rangeFrom  DATETIME,
    @rangeTo    DATETIME
  )
RETURNS SMALLINT
AS
BEGIN
   DECLARE @result SMALLINT
   IF @value < ISNULL(@rangeFrom,'1753-01-01 00:00:00.000') 
   BEGIN
      SET @result = -1
   END
   ELSE IF @value > ISNULL( @rangeTo, '9999-12-31 11:59:59.997' )
   BEGIN
      SET @result = 1
   END
   ELSE
   BEGIN
      SET @result = 0
   END
   RETURN @result
END

posted by Kirby | 30-Aug-2005 1:54 PM | comments (0)

Add Your Comment

Comment: (No HTML)
Name:
E-mail/Web site:	Your e-mail/web site will not be published on this site. It is optional and will only be used by me should I need to contact you directly.
	Remember me? By checking this option, this site will remember your name and e-mail/web site on future visits. Uncheck this option to have the site not remember who you are on future visits.
	Enter the code shown above: