SQL Server SET DATEFIRST scope

Question

I'm using SS 2005 if that

I've seen sample code like

DECLARE @restore = SELECT @@DATEFIRST
SET DATEFIRST 1
SELECT datepart(dw,ADateTimeColumn) as MondayBasedDate,.... FROM famousShipwrecks --
SET DATEFIRST @restore

Suppose while the query is running another query sets DATEFIRST?

If another query relies on datefirst being 7 (for example) and doesn't set it, and runs while my query is running, that's his problem for not setting it? or is there a better way to write queries that depend on a given day being day number 1.

Answer 1

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

Answer 2

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!