The DATEFIRST and DATEPART Relationship

At first glance, the way SQL Server defines the days of the week can be confusing. The definition is a mix of two values—the DATEFIRST value and the DATEPART value—the second of which relies on the first for its meaning.

First, let's look at how DATEFIRST works. The database management system (DBMS) needs to know which day is the first day of the week. SQL Server assigns each day a default integer: For example, Monday is 1 and Sunday is 7. This default setting corresponds to the European standard in which the first day of the week is Monday. But in the United States, Sunday is the first day of the week. The master database's syslanguages table has a DateFirst column that defines the first day of the week for a particular language. When you set the default language for your SQL Server to US English, SQL Server sets DATEFIRST to 7 (Sunday) by default.

After DATEFIRST defines the first day of the week for SQL Server, you can rely on DATEPART to return integer values when dw (day of week) is the DATEPART. When Sunday is the first day of the week for the SQL Server, DATEPART(dw,) will return 1 when the date is a Sunday and 7 when the date is a Saturday. (In Europe, where Monday is the first day of the week, DATEPART(dw,) will return 1 when the date is a Monday and 7 when the date is a Sunday.)

If your business is international, it's important to explicitly set the DATEFIRST value early in your script to remove ambiguity from the SQL Server's setup and ensure that the script works correctly. European businesses would code SET DATEFIRST 1 and adjust the DATEPART code accordingly so that Monday is the first day of the week.