SQL Server SET DATEFIRST scope
@@DATEFIRST is local to your session. You can verify it by opening to tabs in Sql Server Management Studio (SSMS). Execute this code in the first tab:
SET DATEFIRST 5And verify that it doesn't affect the other tab with:
select @@datefirstSee this MSDN article.
Just an additional point, if you want to avoid setting DATEFIRST you can just incorporate the value of DATEFIRST in your query to find your required day as :
SELECT (datepart(dw,ADateTimeColumn) + @@DATEFIRST) % 7) as 'MondayBasedDate' , ... FROM famousShipwrecks --Then you dont need to worry about restoring it at all!
To setup a parameterized first day of the week, the following should work
DECLARE @FirstDayOfWeek INT = 1;DECLARE @DateTime DATETIME = '2015-07-14 8:00:00';SELECT (DATEPART(weekday, @DateTime) + @@DateFirst - @FirstDayOfWeek - 1) % 7 + 1;