First day of the week

Given a particular date how do you determine the start date of that week? But more importantly what is the start of the week?

The first column returned in the script below will return the start of the week as determined by the @@DATEFIRST setting. Using a default setup with the connections made using us_english the @@DATEFIRST value is 7, which means that the start of week is Sunday.
If the language was set to Italian the value would be 1 which is Monday. Alternatively this setting can be changed using SET DATEFIRST 1, which would give the same result.

A better solution is to make sure that the query is not altered by these settings and instead returns the same results regardless.

SELECT @dt [dt]
,DATEADD(DAY,-DATEPART(dw,@dt)+1,@dt) [First Day Of Week (SET DATEFIRST X Dependent)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-2,@dt)))%7),@dt) [First Day Of Week (Monday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-3,@dt)))%7),@dt) [First Day Of Week (Tuesday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-4,@dt)))%7),@dt) [First Day Of Week (Wednesday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-5,@dt)))%7),@dt) [First Day Of Week (Thursday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-6,@dt)))%7),@dt) [First Day Of Week (Friday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,@dt))%7),@dt) [First Day Of Week (Saturday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-1,@dt)))%7),@dt) [First Day Of Week (Sunday)]


Leave a Reply