DATEFIRST setting in SQL Server
DATEFIRST setting in SQL Server determines the first day of the week for the purpose of displaying and interpreting dates. This setting can be set at the server level or the session level, and affects the behavior of several built-in functions that deal with dates, such as DATEPART, DATENAME, and DATEADD.
Setting the DATEFIRST Value
To set the DATEFIRST value at the server level, use the following syntax:
|
1 2 3 4 5 6 7 8 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'datefirst', 7; GO RECONFIGURE; GO |
This will set the first day of the week to Sunday (7) for the entire server. To set the DATEFIRST value at the session level, you can use the following syntax:
|
1 |
SET DATEFIRST 7; |
This will set the first day of the week to Sunday for the current session only.
Examples
Here are some examples of how the DATEFIRST setting can be used in SQL Server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Set the DATEFIRST value to Monday (1) for the current session SET DATEFIRST 1; -- Get the name of the day of the week for a given date SELECT DATENAME(dw, '2022-01-01') AS "Day of Week"; -- Result: "Saturday" -- Get the number of the week of the year for a given date SELECT DATEPART(wk, '2022-01-01') AS "Week of Year"; -- Result: 1 -- Add 7 days to a given date SELECT DATEADD(d, 7, '2022-01-01') AS "Date + 7 Days"; -- Result: "2022-01-08" |
As you can see, the DATEFIRST setting affects the output of functions such as DATENAME and DATEPART, as well as the behavior of the DATEADD function.
Additional Resources
For more information on the DATEFIRST setting and how it can be used in SQL Server, see the following resources:
- DATEFIRST (Transact-SQL) – Microsoft Docs
- SET DATEFIRST (Transact-SQL) – Microsoft Docs
- Date and Time Functions (Transact-SQL) – Microsoft Docs
Old Article
The @@DATEFIRST returns the current value of SET DATEFIRST for a specific session. By default, for all U.S English environments this value is 7. In simple terms this value dictates what should be the starting day as shown below:
- Monday = 1
- Tuesday = 2
- Wednesday = 3
- Thursday = 4
- Friday = 5
- Saturday = 6
- Sunday = 7
The reason why I am writing this post is I wanted to help others that maybe having some oddities with the output of their dates, as an example we had an issue where by the dates that where being inserted into a table by executing a stored procedure which should show the date of a given Friday or Saturday from a scheduled job where showing a day less then what they should have been, meaning that February 7th 2020 which is a Friday was showing as February 6th 2020 which is a Thursday.
Below is a snippet of code to show the area of the stored procedure where this date was being calculated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@year2StartDate=dbo.getDateFromParts(26,12,2020-1) @dow_yearStartDate= DatePart(Weekday,@year2StartDate) Case CM.cd_Cod when 'Sat' then 6 when 'Fri' then 5 when 'Thu' then 4 when 'Wed' then 3 when 'Tue' then 2 when 'Mon' then 1 when 'Sun' then 0 else null End AS dow_Changeoverday Cursor here …. Select @availSectionStartDate=DateAdd(day,@dow_ChangeOverDay-@dow_yearStartDate,@year2StartDate) |
The platform that we are using is Managed SQL Server from Azure. I got down to debugging to work out why this was happening, I confirmed that the user that was running the stored procedure was configured for British culture. I then checked to make use the session that I was connected to was outputting 1 after executing the below:
SELECT @@DATEFIRST
This indeed did show the correct result of 1 being Monday. The next step was to check what the output of @@DATEFIRST was when running from a context of a scheduled job. I created a simple job with 1 job step with the following T-SQL:
PRINT ‘The Value of @@DATEFIRST is => ’ + CAST(@@DATEFIRST AS NVARCHAR(2))
I then proceeded to execute this Job, once completed I checked the Job history and viewed the Log file for this job. The output of @@DATEFIRST was showing as 7 being Sunday. This was why our Dates were slightly off by one.
To resolve this within the stored procedure in question at the beginning I added the following:
SET DATEFIRST 1;
After adding this the dates were showing correctly.
