Link to home
Start Free TrialLog in
Avatar of Cluskitt
CluskittFlag for Portugal

asked on

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?
SOLUTION
Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cluskitt

ASKER

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.
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.
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?
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
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.
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?
If that returns the same values as:
SET DATEFIRST 1 SELECT DATEPART(wk,GETDATE())
Then that is what I need. ;)
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?
OK, let me see what SQL tells me about week numbers then I can figure something out :-) .
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.
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:

SELECT
FROM dbo.existing_table
WHERE
    invoice_date_weeknum BETWEEN 20 AND 24
GROUP BY
    invoice_date_weeknum

[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

SELECT
    date,
    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
FROM (
    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'.
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)
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.
>> 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.
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.
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.
Sorry about this. I really thought I had assigned an A grade. The datediff tip alone would be worth it.