Datefirst statement in SQL Server

The SET Datefirst statement in SQL Server set the First day of the week for the current session. This setting affect how date related function are operations interpret the day of the week. If our default language is US English , then by default 7 (Sunday) is set as the First day.

Value for the set Datefirst

Value	First day of the week	Comment
1	Monday	Default for many Europe countries
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday	Default in US

 @@datefirst function is used to get the first day of the week.

select @@datefirst  as 'First day of the week'

It returns 7. In my system default language is US English.

We can also set the Datefirst for a day. Let’s see here we want to set the first day as Tuesday.

Set DateFirst 2

Set Datefirst is session level setting. It will impact only an session level and it do not change the global setting for the SQL Server. It affect DatePart(wwekday,date) but not Format(date,ddd).

Always set Datefirst before using the datepart to ensure consistency.