Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Date function that determines date range based on weekday


By:   |   Last Updated: 2009-06-10   |   Comments (2)   |   Related Tips: 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@counterGETDATE())) 
     
     
IF @day_name_calc @day_name
       
BEGIN
         SELECT 
@hits @hits 1
         
SELECT @daysearch DATEADD(d@counterGETDATE())
       
END

     SELECT 
@counter (@counter + (@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.field1T1.field2T1.field3T1.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, -1AS StartDate,
       
dbo.DAYSEARCH('Wednesday'1, -1AS 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.
  • Review these additional tips on date functions on MSSQLTips.com
  • Other tips by the author are available here.


Last Updated: 2009-06-10


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 08, 2011 - 10:23:26 AM - Keith Back To Top

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

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


Learn more about SQL Server tools