SQL Server Date function that determines date range based on weekday

By: Tim Ford   |   Updated: 2009-06-10   |   Comments (2)   |   Related: > Dates

Problem

Occasionally I find myself dusting off my old developer coffee mug and sitting down in front of my laptop to do a bit of creative coding when one of our current developers runs up against something they can't get their head around.  Recently this was the case with a stored procedure they were attempting to create that needed to return results for a date range based around the day of the week, not the specific date of the month. 

The customer needed to have a result set based upon "the Thursday through Wednesday timeframe from last week" as it was so succinctly put.  If the stored procedure was always going to be scheduled to run on a given day of the week this would be easily calculable, however, the end user needed to have flexibility to run this stored procedure on either Friday, Saturday, or Sunday depending on the situation and the developer did not want to force the user to enter a start and end date in order to mitigate bad parameters causing bad reporting.  Generating the start date and end date parameters then became a challenge I really dug into.  I wanted to be able to come up with a reusable code base, materialized as a user-defined function as shown below.

Solution

The function I created makes use of a few existing system date-based functions: DATEADD(), and DATENAME(). 

• DATEADD() accepts three parameters: datepart, number, and date.  This function returns a datetime datatyped value based upon a starting date (the date parameter) and the number of increments added or subtracted from that date (signified by the datepart and number parameters.) 
• DATENAME() on the other hand returns a character string that signifies the value of the datepart for a given date (passed in as the datepart and date parameters respectively.)  Without looking at the code this may appear a little confusing:

Here is the function that I created that uses both of these system functions.

CREATE FUNCTION dbo.DAYSEARCH(@day_name VARCHAR(9), @step_count INT, @direction smallint) 
RETURNS datetime 
AS 
BEGIN 
/* 
Returns a date based upon criteria to find a specific day-of-week  
 for a specific number of "steps" forward or backward. 
 For instance, "last Wednesday" or "two Thursdays from today". 
@day_name = day of week to find ie. Monday, Tuesday... 
@step_count = number of iterations back for a specific day: 
--------> "1 Last Monday " =  1 
--------> "3 Thursdays from now" = 3 
@direction: 
--------> -1 if Past 
--------> 1 if Future 
*/ 

 DECLARE @daysearch datetime 
 DECLARE @counter smallint 
 DECLARE @hits smallint 
 DECLARE @day_name_calc VARCHAR(9) 

 SELECT @counter = @direction 
 SELECT @hits = 0 

 WHILE @hits < @step_count 
   BEGIN 
     SELECT @day_name_calc = DATENAME(weekday , DATEADD(d, @counter, GETDATE()))  
      
     IF @day_name_calc = @day_name 
       BEGIN 
         SELECT @hits = @hits + 1 
         SELECT @daysearch = DATEADD(d, @counter, GETDATE()) 
       END 

     SELECT @counter = (@counter + (1 * @direction)) 
   END 
 RETURN @daysearch       
END

The DAYSEARCH() user-defined function requires three parameters:

• @day_name, which is the name of the day you are searching for (ie. 'Monday', 'Tuesday', etc...)
• @step_count, signifying the number of cycles forward or backward you're searching for (last Monday would require a value of 1, three Mondays ago would require a value of 3)
• @direction, which must have one of two values: -1 to search backward in time or 1 to search forward to the future.

From that point is simply the matter of utilizing the DATENAME() and DATEADD() functions in conjunction to return the value of the DATENAME() function for each day forward or backward in time (depending upon the @direction parameter).  When the correct day is returned (the result of the DATENAME() function equals the @day_name parameter supplied by the user or procedure code) a "hit" is recorded.  The iterations continue to run until the number of "hits" matches the @step_count parameter of the DAYSEARCH() function.  When that occurs, the function's return value records the date generated by the corresponding DATEADD() function and returns it to the user as the function output.

Once I created DAYSEARCH() I was able to turn it over to the developer to use from within the stored procedure he was creating.  In order to signify the previous Thursday-to-Wednesday timeframe he simply had to set his start and end values in his query's WHERE clause in a fashion similar to the simplified code block displayed below:

SELECT T1.field1, T1.field2, T1.field3, T1.field4 
FROM table1 T1 
WHERE T1.start_date = dbo.DAYSEARCH('Thursday', 2, -1) 
 AND T1.end_date = dbo.DAYSEARCH('Wednesday', 1, -1)

Here is another example to show you what the output looks like if we want to know the time now, the date on Thursday two weeks ago and the date on Wednesday of last week.

SELECT GETDATE() AS TimeNow,  
       dbo.DAYSEARCH('Thursday', 2, -1) AS StartDate, 
       dbo.DAYSEARCH('Wednesday', 1, -1) AS EndDate 

Here is the output for the above query.

And here is a calendar showing the current date (TimeNow=2009-06-09), the Thursday from two weeks ago (StartDate = 2009-05-28) and the Wednesday of last week (EndDate=2009-06-03).

Next Steps

• This function may not meet your exact needs, but now you can see how you can use the system function to build your own custom date functions.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips