The SQL Server DATEFIRST Set Function will be set on the first Day of the week from 1 through 7. If your default language is US English, then by default 7 (Sunday) is set as the first Day. The basic syntax of the DATEFIRST is as shown below.
SET DATEFIRST { number (or number_variable) }
-- For example,
SET DATEFIRST 4;
The following SQL Server table will show you the Value and its corresponding weekday name
| Value | First Day of the Week |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 | Sunday |
SQL DATEFIRST Example
In this SELECT example, we will show how the SET DATEFIRST will affect the weekday. Please refer to the SQL SELECT statement available on our SQL Server Tutorial page.
-- Default first Day
SELECT @@DATEFIRST AS 'First day of the Week'
-- Set the DateFirst Value to 3 (Wednesday)
SET DATEFIRST 3;
-- Now let me select the first Day Value
SELECT @@DATEFIRST AS 'First day of the Week'
SELECT GETDATE() AS 'Today',
DATEPART(dw, GETDATE()) AS 'Today Number'
Though today is a Wednesday, SQL DATEPART of dw is returning 1 (instead of 4) because we set the first day like Wednesday. So, the counting will start from Wednesday ( wed = 1, Thursday = 2, Friday = 3 ..).
TIP: Please refer to the SQL @@DATEFIRST article from the list of SQL Server date and time functions.
