How to Set First Day of the Week in SQL Server

Tutorialsrack 30/04/2019 SQL SQL Server

In this article, we will learn how to Set the First Day of the Week in SQL Server.

This applies to SQL Server (starting with 2008) or higher and Azure SQL Database

The DATEFIRST statement sets the first day of the week for the session to a number from 1 through 7.

The syntax for SQL Server and Azure SQL Database

SET DATEFIRST { number | @number_var };

Where,
number | @number_var: Is an integer that indicates the first day of the week.

In SQL Server, the values for the days of the week are the following:

The first day of the week is based on your language settings of the server. The default setting for us_english is 7 (Sunday)

For example, for us_english language setting your session’s DATEFIRST setting has a default value 7 (it means that Sunday is the first day of the week), and for German, it is 1 (Monday is the first day of the week).

If you want to change the language setting and set it to “us_english”, the statement is as follow:

SET LANGUAGE us_english;  -- After setting the ‘us_english’ Language
SELECT @@DATEFIRST as 'Default value of the Week First Day';

If you want to change the language setting and set it to “German”, the statement is as follow:

SET LANGUAGE German; -- After setting the ‘German’ Language
SELECT @@DATEFIRST as 'Default value of the Week First Day';

SELECT @@DATEFIRST;

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

You can override DATEFIRST's current value by using the following statement given below.

For example, you want to sets Tuesday as the first day of the week by using the following statement:

SET DATEFIRST 2;
--Now your Default value of the Week First Day is set to 2 which is Tuesday

I hope this article will help you to understand that learning how to Set the First Day of the Week in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts