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 ..)
