By: Tim Ford | 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
