In SQL Server, you can use SET DATEFIRST to set the first day of the week.

The first day of the week can be different, depending on the language being used. For example the default for us_English is 7 (Sunday), whereas the default for Deutsch (German) is 1 (Monday).

This article demonstrates how to change the first day of the week without changing the language.

Syntax

First, the syntax goes like this:

SET DATEFIRST { number | @number_var }

Where number | @number_var is an integer that indicates the first day of the week. This is a number from between 1 and 7.

The following table shows the mapping between the number and the day of the week.

Value	First day of the week is
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday

So a value of say, 3, would set Wednesday as the first day of the week.

Example 1 – Basic Usage

Here’s an example to demonstrate the syntax.

SET DATEFIRST 1;

This sets the first day of the week to Monday (we can see from the above chart that a value of 1 maps to Monday).

We can view the current setting by using @@DATEFIRST to return the current value of SET DATEFIRST.

SELECT @@DATEFIRST AS 'Result';

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – Override the Language Setting

This example demonstrates how the SET DATEFIRST setting fits in with the language settings.

SET LANGUAGE us_english;  
SELECT 
    @@LANGUAGE AS 'LANGUAGE Before',
    @@DATEFIRST AS 'DATEFIRST Before';

SET DATEFIRST 1;
SELECT
    @@LANGUAGE AS 'LANGUAGE After',
    @@DATEFIRST AS 'DATEFIRST After';

Result:

+-------------------+--------------------+
| LANGUAGE Before   | DATEFIRST Before   |
|-------------------+--------------------|
| us_english        | 7                  |
+-------------------+--------------------+
Changed language setting to us_english.
+------------------+-------------------+
| LANGUAGE After   | DATEFIRST After   |
|------------------+-------------------|
| us_english       | 1                 |
+------------------+-------------------+

So we can see that the language remains the same, but the first day of the week has changed.

This example uses SET LANGUAGE to set the language of the current session (which as mentioned, is used to determine the first day of the week). The language setting also implicitly sets the date format. If needed, you can use SET DATEFORMAT to override this setting for the date format.

To see what DATEFIRST settings map to each language, here’s a List of All Languages and Associated Date Formats in SQL Server 2017.

You can also use the sp_helplanguage stored procedure to return that list. To do this, see How to Find the Date Formats Used for a Specific Language in SQL Server (T-SQL).