Using the information in this map, I started experimenting with an equation to get DATEPART's return value back to the internal value (i.e., to "normalize" it). The equation I came up with is
((@SqlWeekDay + @SqlDateFirst - 1 - 1) % 7) + 1
where @SqlWeekDay is the value returned by DATEPART(dw, @SomeDate) and @SqlDateFirst is the value returned by @@DATEFIRST. I incorporated the equation into the dbo.lmsf_MondayBasedWeekday function, which Listing 1 shows.
Listing 1: The dbo.lmsf_MondayBasedWeekday Function |
 |
This function returns a "normalized" Monday-based day of the week, as if DATEFIRST is set to 1. It will always return 1 for Monday, 2 for Tuesday, and so on, no matter what region you're in and no matter what DATEFIRST has been set to.
The dbo.lmsf_MondayBasedWeekday function is useful in and of itself. Plus, you can incorporate it into other code. For example, I recently needed to summarize some pricing data on a weekly basis, so I needed to get a week's ending date based on a date that I passed in. In this case, the price points could be from any day Monday through Friday. The client used Friday as the week's end, so if the date I passed in was a Monday, I wanted that coming Friday's date as my result. If the date I passed in was a Friday, I wanted the same date.
Using the dbo.lmsf_MondayBasedWeekday function, I created the dbo.lmsf_NextSpecificWeekday function in Listing 2.
Listing 2: The dbo.lmsf_NextSpecificWeekday Function |
 |
The dbo.lmsf_NextSpecificWeekday function takes two parameters:
- @FromDate. You use the @FromDate parameter to specify the date from which you want to start your search. The parameter has the data type of date (which is new to SQL Server 2008), so it follows the standard T-SQL conventions for handling dates.
- @MondayBasedWeekDay. You use the @MondayBasedWeekDay parameter to specify the day of the week you're looking for. Do you want the next Friday or the next Sunday? You need to specify a Monday-based value, which means that you'd specify 1 for Monday, 2 for Tuesday, 3 for Wednesday, and so on.
For example, let's say you want to find the first Friday that falls after Tuesday, November 11, 2009. You'd pass in @FromDate = '11/3/2009' and @MondayBasedWeekDay = 5. The dbo.lmsf_NextSpecificWeekday function then returns the result of Friday 11/6/2009. The best part is that the parameters and the result will be consistent, no matter what DATEFIRST is set to.
You can download the dbo.lmsf_MondayBasedWeekday and dbo.lmsf_NextSpecificWeekday functions by clicking the 102470.zip hotlink at the top of the page. The functions have been tested on SQL Server 2008 machines. Although I haven't tested them on SQL Server 2005 and SQL Server 2000, they'd likely work if you change the date data types to datetime.