The SQL DATEFIRST Set Function will set 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 in SQL Server is
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 example, we will show how the SET DATEFIRST will affect the weekday?.
-- 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, 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 ..)
