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:<ul><li>Date to check</li><li>Beginning date of the date range</li><li>Ending date of the date range</li></ul>
The function returns one of the following:<ul><li>-1 if the date is before the range</li><li>0 if the date is within the range</li><li>1 if the date is beyond the range</li></ul>
Here’s the code for those who want it.<pre>
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
</pre>
Posted in uncategorized. Tagged in .