By: Tim Ford | Last Updated: 2009-06-10 | Comments (2) | Dates
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.
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.
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:
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.
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).
- 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
About the author
View all my tips