10

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
Pride Fallon
  • 107
  • 1
  • 1
  • 5

4 Answers4

25

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

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    So if you're writing a stored procedure that's going to constitute the entire session - as the one and only data source for a crystal report where there's no chance the SP will ever change or be reused - then there's no point restoring the original value? – Pride Fallon May 20 '09 at 12:56
  • 1
    Agreed, but maybe Crystal Reports caches the connection. So it can't hurt to restore the original value. – Andomar May 20 '09 at 13:05
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!

erick barreat
  • 186
  • 12
Mongus Pong
  • 11,337
  • 9
  • 44
  • 72
  • 5
    This does not give a MondayBasedDate, but one where sunday is 1 and monday is 2. To make monday 1 and sunday 7 use the following changes: datepart(WEEKDAY, ADateTimeColumn) + @@DATEFIRST - 2 ) % 7 + 1 – Jan Obrestad Sep 15 '10 at 08:50
2

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', DateColumn) % 7 + 1 AS MondayBasedDay

For Sunday based weeks (America) use:

SELECT DATEDIFF(day, '17530107', DateColumn) % 7 + 1 AS SundayBasedDay

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

Michel de Ruiter
  • 7,131
  • 5
  • 49
  • 74
  • [Like old Philip Stanhope used to say](https://stackoverflow.com/a/3310588/3585500), "Those who forget history are doomed to use DATEFIRST." – ourmandave Aug 04 '23 at 17:23
0

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;
Gazi
  • 21
  • 4