The SET DATEFIRST function is used to set the first day of the week from number 1-7. In order to know the current first day of the week, @@DATEFIRST is used. T-SQL example for the same is given below.
The DATEFIRST settings effects the day of the week value for a date value returned from the DATEPART function. Let’s see how it is changed.
In above snapshot, day of the week is calculated using DATEPART function. The default value for the first day of the week is Sunday – 1. Thus, the first query returns DayOfWeek as 1 as its Sunday today. The first day of week is then changed to Thursday – 4, thus with Thursday as 1st day of week Sunday becomes the 4th day of the week.