Normalizing the First Day of the Week

Handling the days of the week in T-SQL code can get complicated, especially if you want your code to work across multiple SQL Server machines, regardless of their regional settings. When you use the DATEPART function with the dw parameter, you get an integer that tells you the day of the week for the given date. (See also, "SQL Server 2008's T-SQL Features," "T-SQL's Datetime Data Type" and "T-SQL Function Finds Almost Any Kind of Date").

The integer that the DATEPART function returns depends on the value of DATEFIRST, which specifies the first day of the week. The possible DATEFIRST values and the days they represent are:

I'll refer to these DATEFIRST values as the "internal" values for the days of the week because they're internal to SQL Server.

You can use the @@DATEFIRST variable to return the value currently set for DATEFIRST. When SQL Server is set to the regional setting of US English, DATEFIRST's default value is 7, which means that Sunday is considered the first day of the week. Other regional settings can have different default values. For example, when SQL Server is set to the regional setting of Italian, DATEFIRST's default value is 1, which means that Monday is considered the first day of the week.

You can override DATEFIRST's current value by using the command

SET DATEFIRST #

where # is the internal value representing the day of the week you want to set DATEFIRST to. For example, the command

SET DATEFIRST 2

sets Tuesday as the first day of the week.

Because the DATEPART function depends on a value that can differ between regional settings and that can be changed, you can run into problems when using the function. For example, suppose you're working at your company's New York headquarters and you want to find out the day of the week that October 5, 2009, falls on, so you execute the code

SELECT  CASE DATEPART(dw, '2009-10-05')    WHEN 1 THEN 'Sunday'    WHEN 2 THEN 'Monday'    WHEN 3 THEN 'Tuesday'    WHEN 4 THEN 'Wednesday'    WHEN 5 THEN 'Thursday'    WHEN 6 THEN 'Friday'    WHEN 7 THEN 'Saturday'  END

Because SQL Server is set to the regional setting of US English (DATEFIRST = 7), Sunday is considered the first day of the week. Thus, if October 5 falls on a Sunday, DATEPART would return 1. If October 5 falls on a Monday, DATEPART would return 2, and so on. In this scenario, the code returns the correct result of Monday.

However, this code will return the wrong result if DATEFIRST has a different value than you expected. Let's say you're visiting a branch office in Venice, Italy, and you run this code on one of its SQL Server machines. Because the default value for the Italian regional setting is 1, Monday is considered the first day of the week. So, if October 5 falls on a Sunday, DATEPART would return 7. If October 5 falls on a Monday, DATEPART would return 1, and so on. Thus, the code will return an incorrect result of Sunday.

Because I often write code that runs on multiple servers worldwide, I decided to write an algorithm that calculates a "normalized" value for the day of the week based on the current date and the DATEFIRST value. By "normalized" I mean something that will return a value of 1 for Monday, no matter what DATEFIRST is set to, so that code that's dependent on a day of the week will always work correctly on any SQL Server machine.

Trying to write the algorithm got pretty tricky pretty quickly, so I created the map in Figure 1.

This map shows what DATEPART will return for each possible DATEFIRST (DF) value. In the first column, the numbers in parentheses are DATEFIRST's internal values—and the values I wanted to represent the days of the week.

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.

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.

The dbo.lmsf_NextSpecificWeekday function takes two parameters:

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.