Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Sets the first day of the week to a number from 1 through 7.
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
Transact-SQL syntax conventions
SET DATEFIRST { number | @number_var }
SET DATEFIRST 7 ;
Is an integer that indicates the first day of the week. It can be one of the following values.
Value | First day of the week is |
---|---|
1 |
Monday |
2 |
Tuesday |
3 |
Wednesday |
4 |
Thursday |
5 |
Friday |
6 |
Saturday |
7 (default, U.S. English) |
Sunday |
To see the current setting of SET DATEFIRST, use the @@DATEFIRST function.
The setting of SET DATEFIRST is set at execute or run time and not at parse time.
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.
Like all SET Statements, SET DATEFIRST affects the current session.
Requires membership in the public role.
The following example displays the day of the week for a date value and shows the effects of changing the DATEFIRST
setting.
-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7;
SELECT CAST('1999-1-1' AS datetime2) AS SelectDate
,DATEPART(dw, '1999-1-1') AS DayOfWeek;
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1999-1-1
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when you start with Sunday as day 1.
SET DATEFIRST 3;
-- Because Wednesday is now considered the first day of the week,
-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the
-- week. The following DATEPART function should return a value of 3.
SELECT CAST('1999-1-1' AS datetime2) AS SelectDate
,DATEPART(dw, '1999-1-1') AS DayOfWeek;
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today