January 4, 2006 at 4:42 am
What is up with the sql server weeks ?
I want to calculate the weeks with these rules:
First day of week: Saturday
First week of year: Starts on jan 1th
I thought sql server used starts on jan 1th as default ?
If i configure my outlook with start saturday, starts on 1th jan then it
is correct so what do i need to do to calculate the correct weeks ?
Anyone ?
2005-2006
SET DATEFIRST 6 -- Week starts on saturday
select DATEPART(wk, '2005-12-30') --Should be 52, is 52
select DATEPART(wk, '2005-12-31') --Should be 52, is 53 ??
select DATEPART(wk, '2006-01-01') --Should be 01, is 01
select DATEPART(wk, '2006-01-02') --Should be 01, is 01
select DATEPART(wk, '2006-01-03') --Should be 01, is 01
select DATEPART(wk, '2006-01-04') --Should be 01, is 01
select DATEPART(wk, '2006-01-05') --Should be 01, is 01
select DATEPART(wk, '2006-01-06') --Should be 01, is 01
select DATEPART(wk, '2006-01-07') --Should be 02, is 02
2004-2005
SET DATEFIRST 6 -- Week starts on saturday
select DATEPART(wk, '2004-12-30') --Should be 53, is 53
select DATEPART(wk, '2004-12-31') --Should be 53, is 53
select DATEPART(wk, '2005-01-01') --Should be 01, is 01
select DATEPART(wk, '2005-01-02') --Should be 01, is 01
select DATEPART(wk, '2005-01-03') --Should be 01, is 01
select DATEPART(wk, '2005-01-04') --Should be 01, is 01
select DATEPART(wk, '2005-01-05') --Should be 01, is 01
select DATEPART(wk, '2005-01-06') --Should be 01, is 01
select DATEPART(wk, '2005-01-07') --Should be 01, is 01
select DATEPART(wk, '2005-01-08') --Should be 02, is 02
2006-2007
SET DATEFIRST 6 -- Week starts on saturday
select DATEPART(wk, '2006-12-29') --Should be 52, is 52
select DATEPART(wk, '2006-12-30') --Should be 01, is 53 ??
select DATEPART(wk, '2006-12-31') --Should be 01, is 53 ??
select DATEPART(wk, '2007-01-01') --Should be 01, is 01
select DATEPART(wk, '2007-01-02') --Should be 01, is 01
select DATEPART(wk, '2007-01-03') --Should be 01, is 01
select DATEPART(wk, '2007-01-04') --Should be 01, is 01
select DATEPART(wk, '2007-01-05') --Should be 01, is 01
select DATEPART(wk, '2007-01-06') --Should be 02, is 02
January 4, 2006 at 5:44 am
Jan 1st 2005 = Saturday
7 days * 52 weeks = 364 days therefore Dec 31st 2005 = week 53.
Jan 1st 2006 = Sunday which is day 2.
Dec 29th 2006 is therefore day 364 which is week 52
Dec 30th 2006 is therefore day 365 which is week 53
Dec 31st 2006 is therefore day 366 which is week 53
January 4, 2006 at 7:43 am
So why does outlook say it is week 1 on 30.12.2006 and 31.12.2006.
I mean start of week is saturday, which is 30.12.
Are there two ways of calculating the jan 1st rule ?
Seems like outlook uses week 1 is the week which contains jan 1st.
And sql server uses week 1 is starts with jan 1st. And doesnt care about the first day of the week..
I'm confused, does anyone have an udf / algo which gives me the same weeks as outlook when using startofweek saturday and the jan 1st rule ?
Or can anyone give me a url or something that explains in detail all the different week formulas ? Can't seem to find one.
January 5, 2006 at 2:56 am
Probably it is a matter of personal preference. I think that Outlook is wrong if it says that 30.12.2006 is week 1. I would definitely say it is week 53, because it is one of the last days of the year... so it can hardly be first week. If you sum sales of week 1 year 2006, do you also add sales from 30.12.2006? I doubt it... and precisely this could happen, if there are several weeks evaluated as "1" during the same year.
IMHO weeks are generally unreliable and should be avoided at all costs. It can cause great confusion. Your week starts on Saturday, weeks in central Europe start on Monday and in many other countries on Sunday... and no one knows which days are meant if you say "week 24". Why do you need the UDF to translate week numbers into "Outlook style"? Couldn't you achieve the same in some other way?
January 5, 2006 at 10:16 am
We use a calendar table. It lists every day in the year and its corresponding values.
day fiscal week calendar week iso date fiscal month calendar month
ie. 1-1-2006 40 1 20060101 9 1
We then query against that to get the information our 3 database systems need to maintain accurracy for reporting.
Tom
January 5, 2006 at 1:41 pm
It seems to me that Outlook is wrong, or at least incomplete. Week 1 of 2006 starts on 31.12.2005. Week 53 of 2005 starts on the same date. They are just two expressions for the same date. To simply say that the date is week 1 ignores the critical information as to what year you are talking about. Its kinda like asking "How much does that box weigh?" "Seven." Seven what? Pounds? Tons?
When you query for the week for a given date, you've set the context for the year you're interested in, and SQL returns 53. If all you need is the Outlook-like week number I'd write a function that returned the values you want, but be VERY careful, because once the function is out there, someone will mistakenly use it in a calculation and get some wacky results.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply