Force MS SQL 2005 to start week on a Monday (globally)

Is there any way to set MS SQL to start weeks on a monday, without having to use SET DATEFIRST 1 with all statements?
LVL 18
Who is Participating?
Scott PletcherSenior DBACommented:
Not AFAIK.  I don't know if a logon trigger that issues a SET DATEFIRST would work at all, even in a limited way.

You can write your code so that is not dependent on DATEFIRST settings, of course :-).

For example, the link from above states:
I code using @@DATEFIRST and DATEFIRST especially identifying the non-work days like weekends in a week.

Unnecessary.  You can easily determine weekends w/o ever considering DATEFIRST in any way.

For *example* (just to show the possibilities):

DECLARE @date_to_check datetime
SET @date_to_check = GETDATE()
SELECT CASE WHEN DATEDIFF(DAY, 0, @date_to_check) % 7 IN (5, 6) THEN 'Weekend' ELSE 'Weekday' END
James MurrellProduct SpecialistCommented:
@@DATEFIRST property can be set for a sql login or sql user which will effect the all connections of the related user.

The DATEFIRST property or @@DateFirst variable value is configured by the default language of the connected user.

check out
CluskittAuthor Commented:
Well, the link isn't something I'd be too willing to try. Especially because, if I change the language, it will probably also change the decimal/thousands separator. That would completely screw things up. Also, I'm not sure what other implications it might have.

This has become a problem because I had many queries where I used SET DATEFIRST 1, and they worked fine. But there were a few where I didn't use it, because it didn't affect the outcome. These queries had something like WHERE DATEPART(dw,Field1)=DATEPART(dw,Field2). However, recently I created a new column that stores the week day in one of the tables. Therefore, once I populated it, the statement was switched to:
WHERE Field1DayOfWeek=DATEPART(dw,Field2)
And this generated lots of problems on those queries that didn't have the set datefirst 1 (even in the VB code behind queries).

So, I was hoping to avoid any such thing happening in the future.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
Exactly; changing the language has HUGE implications.

>> So, I was hoping to avoid any such thing happening in the future. <<

Don't use: DATEPART(dw, ...)  :-)

So you want Monday to be day#1, right?

Then do this:

DATEDIFF(DAY, 0, Field) % 7 + 1
or, equivalent but clearer:
DATEDIFF(DAY, '19000101', Field) % 7 + 1

Jan 1, 1900 (day 0 in SQL Server) was a Monday (how convenient!).
So, the remainder of the number of days elapsed since that date divided by 7 will tell you what day the current date is, relative to Monday.  And of course this is true REGARDLESS OF ANY DATE SETTINGS OF ANY KIND.

Where possible, I suggest using code that ALWAYS works, regardless of DATEFIRST settings.
CluskittAuthor Commented:
That is a very nice workaround. I'm going to borrow it. ;)

However, I also use datefirst for getting the week number. Do you have a similar workaround for that?
James MurrellProduct SpecialistCommented:
I agree with you both. It is not something I would do, but your question asked "....start weeks on a monday...." this would do that...

as with any answer posted

before you try anyone's possible solution to your questions please always backup your data: it is YOUR responsibility not ours
Olaf DoschkeSoftware DeveloperCommented:
A week number independant on server or locale settings is
SELECT DATEPART(isowk,GetDate())

Open in new window

If the week by iso 8601 is what you need, there you go.

Bye, Olaf.
Scott PletcherSenior DBACommented:
Week number is trickier, unfortunately.

So do you want Monday to always be considered the start of a week, rather than SQL's default of Jan 1 always being a week start?
CluskittAuthor Commented:
If that returns the same values as:
Then that is what I need. ;)
Scott PletcherSenior DBACommented:
So, for 2012, Jan 1 is a Sunday.

What week do you want Jan 1 to be?

What week do you want Jan 2 to be?

What week do you want Jan 8 to be?

What week do you want Jan 9 to be?
Scott PletcherSenior DBACommented:
OK, let me see what SQL tells me about week numbers then I can figure something out :-) .
CluskittAuthor Commented:
SQL considers week 1 as the first week that has Jan 1. However, if your week starts on a monday or on a sunday, that week may change. If Jan 1 is on a sunday, not using SET DATEFIRST 1 will return that week as week 1 and Jan2 as week 1 all the way to Jan 6. If you SET DATEFIRST 1, the exact same situation will return Jan1 as week 1, and Jan2 to 7 as week 2.

This was a big headache for us, especially due to years that have 53 weeks. Also, because we only consider week 1 as the first full week. VB deals ok with this, SQL doesn't. In the end, we added a new column to our Calendar table.
There are a couple places that still use DATEPART(wk,), though. It works properly with datefirst, so if I'm going to start using something else, the returned results have to be the same.
Scott PletcherSenior DBACommented:
The calc below does it.  At least for the years I've tested.  I'll leave further verifications to you, since it is **at least** *very* close.

The actual calc is this:

    (DATEDIFF(DAY, 0, date) / 7) -
    (DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0)) / 7) + 1 AS Week_Number

[The number of Mondays since 19000101 to the current date minus the number of mondays to Jan 1 of the current year, plus 1 (so that the first week of the year is 1 not 0).]

Yes, it's much longer than DATEPART(WEEK, date), **but it's independent of all date settings**.

Also, I suggest adding a computed column to tables to give you the week_number rather than having each query contain the computation.  For example:

ALTER TABLE dbo.existing_table
ADD invoice_date_weeknum AS
    CAST( --just to insure the smallest data type
    (DATEDIFF(DAY, 0, invoice_date) / 7) -
    (DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, invoice_date), 0)) / 7) + 1
    AS tinyint) [PERSISTED]  --persist if you want to avoid the (slight) overhead of recalc'ing

Then you can do this, for example:

FROM dbo.existing_table
    invoice_date_weeknum BETWEEN 20 AND 24

[Computed columns are SWEET! :-) ]

Sample test script I used:

SET DATEFIRST 1 --<<-- chg as needed during testing
    --note that the first column often changes with DATEFIRST but the second never does

DECLARE @year int
SET @year = 2011 --<<-- chg as needed to test different yrs

    DATEPART(WEEK, date) AS Datepart_Week,
    (DATEDIFF(DAY, 0, date) / 7) -
    (DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0)) / 7) + 1 AS Computed_Week
    SELECT CAST(CAST(@year AS char(4)) + '0101' AS datetime) AS date UNION ALL
    SELECT CAST(@year AS char(4)) + '0102' UNION ALL
    SELECT CAST(@year AS char(4)) + '0103' UNION ALL
    SELECT CAST(@year AS char(4)) + '0107' UNION ALL
    SELECT CAST(@year AS char(4)) + '0108' UNION ALL
    SELECT CAST(@year AS char(4)) + '0109'
    --<<-- add additional dates if desired
) AS test_data

Open in new window

Finally, note that by adjusting the "0"/"base" date, you could change to work with any day.

For example, to work with Fridays, change the base date to '19000105'.
CluskittAuthor Commented:
I did say up there that we ended up creating a week number column on our table calendar ;)
Even though I went to check, and we didn't. What we did add was a column for the corresponding date of the previous year. That is, if we're on wednesday of week 3, what was the wednesday of week 3 of the previous year. The 53 weeks a year situations were completely screwing this up.

Come to think of it, we do have the calendar table, with the start of week column. That means that, to count the weeks all I need is a simple count.
CREATE TABLE [dbo].[Calendar](
      [CalendarDate] [datetime] NOT NULL,
      [CalendarYear] [int] NOT NULL,
      [CalendarMonth] [int] NOT NULL,
      [CalendarDay] [int] NOT NULL,
      [DayOfWeekName] [varchar](10) NOT NULL,
      [FirstDateOfWeek] [datetime] NOT NULL,
      [LastDateOfWeek] [datetime] NOT NULL,
      [FirstDateOfMonth] [datetime] NOT NULL,
      [LastDateOfMonth] [datetime] NOT NULL,
      [FirstDateOfQuarter] [datetime] NOT NULL,
      [LastDateOfQuarter] [datetime] NOT NULL,
      [FirstDateOfYear] [datetime] NOT NULL,
      [LastDateOfYear] [datetime] NOT NULL,
      [BusinessDay] [bit] NOT NULL,
      [NonBusinessDay] [bit] NOT NULL,
      [Weekend] [bit] NOT NULL,
      [Holiday] [tinyint] NOT NULL,
      [Weekday] [bit] NOT NULL,
      [CalendarDateDescription] [varchar](50) NULL,
      [LastYear] [datetime] NOT NULL,
      [strEmp] [varchar](250) NULL)
Olaf DoschkeSoftware DeveloperCommented:
To make it short, DatePart(isowk,date) is off by one for this year in comparison with Datepart(wk,date).

Sorry, but I still think you could rather need this and error yourself in taking Datepart(wk,date) for the granted week number. It's not just the first day of a week defining the week number, you can have different defintions of what is the first week of a year. Outlooks week number depend on settings. fiscal years make use of iso week.

There are many equivalent definitions for iso 8601, of which one is, the first week of a year containing a thursday. That means Jan1 is not necessarily in week #1.

But starting with monday, of course, yes. Would it make sense to mention it otherwise?

Bye, Olaf.
Scott PletcherSenior DBACommented:
>> means that, to count the weeks all I need is a simple count. <<

That's true, you can use a table, but the overhead from accessing a table is MUCH higher.

At least be sure the table is *clustered* on CalendarDate.
CluskittAuthor Commented:
The calendar table is pretty much well defined. It has all dates from 2000 to 2025. I've had it run alongside tables that have 100k+ rows with no discernible delay. I have yet to fully examine the queries that still have the datepart week to see if they're still really necessary or not. Either way, this question has been answered. Thanks for all your help.
Scott PletcherSenior DBACommented:
You asked me for another method, remember?
However, I also use datefirst for getting the week number. Do you have a similar workaround for that?

Yeah, thanks for the B grade for answering TWO SEPARATE, DIFFICULT qs.

Good luck with future qs.
CluskittAuthor Commented:
Sorry about this. I really thought I had assigned an A grade. The datediff tip alone would be worth it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.