473,320 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Wrong week numbers

Hi does anyone know how to make MS SQL server 2000 calculate the right week
number around new year ?

According to the help file, MSSQL should follow ISO8601, which ensures that
i.e. 2003 has only 52 weeks. but these two prove it wrong:

Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)
Select datepart(ww,'2004/1/1') --(thursday -> returns 1)

Well - the weeknumber does not change in the middle of the week. Since
2003/12/31 is the third day in the week (with Datefirst set to 1, monday),
the week with the two above dates should have number 1.

Does anyone know the cause or maybe a workaround to this problem ?

Thanks in advance,

Stig Andersen
Jul 20 '05 #1
4 11298
Stig Andersen (wi*******@yahoo.dk) writes:
Hi does anyone know how to make MS SQL server 2000 calculate the right
week number around new year ?
Yes, don't use week numbers. They are a pain rear parts of body.
People keep telling "this and this happens week 43", and I have
to ask when that is. And as you have found out, there is not a
universal opinion on week numbering.

If you need to refer to weeks, please do it the US way, by referring
to the Monday of the week in question. (Or any day, but Sunday, since
there is differening opinions on whether Sunday is the first or last
day of the week.)
According to the help file, MSSQL should follow ISO8601, which ensures
that i.e. 2003 has only 52 weeks. but these two prove it wrong:


I don't which documenation you have read, but Books Online says
about datepart():

The week (wk, ww) datepart reflects changes made to SET DATEFIRST.
January 1 of any year defines the starting number for the week datepart,
for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

See the topic of CREATE FUNCTION for a sample function of computing
week numbers according to ISO rules.

But I would encourage you to spare your users from week numbers
completely.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
On Tue, 21 Oct 2003 14:51:25 +0200, "Stig Andersen"
<wi*******@yahoo.dk> wrote:
According to the help file, MSSQL should follow ISO8601, which ensures that
i.e. 2003 has only 52 weeks. but these two prove it wrong:

Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)
Select datepart(ww,'2004/1/1') --(thursday -> returns 1)
This is a symptom of a known bug. Q200299 might lead you to the
details. (Might not--memory fades.)
Does anyone know the cause or maybe a workaround to this problem ?


Store week numbers in a calendar table. But you might have trouble
expressing the constraint.

--
Mike Sherrill
Information Management Systems
Jul 20 '05 #3
Mike Sherrill (MS*************@compuserve.com) writes:
Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)
Select datepart(ww,'2004/1/1') --(thursday -> returns 1)


This is a symptom of a known bug. Q200299 might lead you to the
details. (Might not--memory fades.)


This is not a bug, but the behaviour is by design.

KB article 200299 discusses a problem with a datepart function,
but that is one in Visual Basic.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Hi,

Thanks for the debate.

I tend to Erlands solution, but unfortunately my customer doesn't. :-)

I found a thorough description of a lot of calendar aspects here:
http://www.tondering.dk/claus/cal/calendar26.html

Chapter 6.7 discusses iso standards regarding weeknumbers:
http://www.tondering.dk/claus/cal/no...l#sec-firstDay

Basically I decided to find the daynumber of december 31st - and then
decided if it was before or after thursday to find out what weeknumber it
had.

Stig Andersen

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...
Mike Sherrill (MS*************@compuserve.com) writes:
Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)
Select datepart(ww,'2004/1/1') --(thursday -> returns 1)


This is a symptom of a known bug. Q200299 might lead you to the
details. (Might not--memory fades.)


This is not a bug, but the behaviour is by design.

KB article 200299 discusses a problem with a datepart function,
but that is one in Visual Basic.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: androtech | last post by:
Hello, I'm looking for a function that returns a date range for a specified week number of the year. I'm not able to find functions like this anywhere. Any pointers/help would be much...
4
by: Dr John Stockton | last post by:
Throughout the world in general, ISO 8601 Week Numbers are used, in which weeks are numbered 01 upwards and Week 01 contains the first Thursday of the Gregorian Calendar Year. There are,...
10
by: Ty Smith via AccessMonster.com | last post by:
I noticed that the week numbers in Stephan Leban's MonthCalendar are not consistent with Microsoft Outlook (they are shifted one week forward). Is there any way I can sync these two up by changing...
10
by: bjaj | last post by:
Hi I have made this msgbox to display the current Week number, but it shows the wrong weekno. Can anyone tell me why ? msg = "WeekNumber: " & DatePart("WW", Date) MsgBox msg It shows...
3
by: Soren Jorgensen | last post by:
Hi, Following code should give the number of weeks in years 1998-2010 for a Danish calendar (on a Danish box) GregorianCalendar cal = new GregorianCalendar(); for(int i = 1998; i < 2010; i++)...
3
by: Steph. | last post by:
Hi, When I use the "Calendar.GetWeekOfYear" function (with "fr-BE" as CultureInfo and Monday as the first day of week) I get : Friday 31/12/2004 : week = 53
0
by: Lee Harr | last post by:
I wrote a function to return the first date of a given week (and a few related functions) : -- return the first date in the given week CREATE or REPLACE FUNCTION week_start(integer, integer)...
10
by: Jim | last post by:
I'm sure this has been asked before but I can't find any postings. I have a table that has weekly inspections for multiple buildings. What I need to do is break these down by the week of the...
0
by: JosAH | last post by:
Greetings, Introduction At this moment we have a TextProcessor, a LibraryBuilder as well as the Library itself. As you read last week a Library is capable of producing pieces of text in a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.