@@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 5

And verify that it doesn't affect the other tab with:

select @@datefirst

See this MSDN article .

You can forget about DATEPART(weekday, DateColumn) and @@DATEFIRST and instead calculate the day of the week yourself.

For Monday based weeks (Europe) simplest is:

SELECT DATEDIFF(day, '17530101', ADateTimeColumn) % 7 + 1 AS MondayBasedDay
FROM famousShipwrecks

For Sunday based weeks (America) use:

SELECT DATEDIFF(day, '17530107', ADateTimeColumn) % 7 + 1 AS SundayBasedDay
FROM famousShipwrecks

This works fine ever since January 1st respectively 7th, 1753.

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;