SQL Server Date function that determines date range based on weekday


By:   |   Updated: 2009-06-10   |   Comments (2)   |   Related: More > 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.

query results

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).

calendar
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.





get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips


Article Last Updated: 2009-06-10

Comments For This Article




Tuesday, November 8, 2011 - 10:23:26 AM - Keith Back To Top (15047)

This formulae using DatePart inside DateAdd does the same thing without the need to loop.

-- -----------------------------------------------------------------
-- Returns the date for the DayOfWeek in relation to the input date
-- Example: Thursday from two weeks ago or Monday at next month end
-- -----------------------------------------------------------------
SET DATEFIRST 7;  -- SQL Server default is 7

Declare @EffDate    date       = '09NOV2011', -- input effective date
        @DayName    varchar(9) = 'MON', -- range MON to SUN
        @WeekOffset int        = 0  -- number of weeks offset; negative for past; positive for future; zero for in current week

Select EffDate     = @EffDate,
       [DayName]   = @DayName,
       WeekOffset  = @WeekOffset,
       [DateFirst] = @@DATEFIRST,
       [DayOfWeek] = DatePart(weekday,@EffDate),
       [Output] = DateAdd(day, - @@DATEFIRST + 7 * ( @WeekOffset + 1 ) +
                  CASE @DayName WHEN 'MON' Then 2
                                WHEN 'TUE' Then 3
                                WHEN 'WED' Then 4
                                WHEN 'THU' Then 5
                                WHEN 'FRI' Then 6
                                WHEN 'SAT' Then 7
                                WHEN 'SUN' Then 1
                  END
                  - DatePart(weekday,@EffDate) , @EffDate)


Wednesday, October 28, 2009 - 7:54:05 AM - WiseOldMan Back To Top (4324)

Very nice.

 I would make a couple of suggestions.  Instead of passing in two parameters for @step_count and @direction, why not just use one (i.e. @step_count = 2, @direction = -1 >> new parameter = -2)

Instead of looping and counting through hits to find the number of weeks desired (suppose it was give me the Wednesday 100,000 weeks ago), just find when the next one occurs and use DATEADD(week, (@step_count * @direction) - 1, @daysearch).

 I'm not sure why day of the week is so hard for so many people.  We typically run year over year reports where we need the day of the week for this year to line up with the same day of the week from last year.  I've seen some pretty ugly SQL code for doing the calculation, when it's just as simple as DATEADD(week, -52, ThisYearsDate),



download














get free sql tips
agree to terms