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. 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:

• 1 = Monday
• 2 = Tuesday
• 3 = Wednesday
• 4 = Thursday
• 5 = Friday
• 6 = Saturday
• 7 = Sunday

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.

Figure 1: Mapping what DATEPART will return for each possible DATEFIRST value

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.