Wrong week numbers

[Stig Andersen]

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,'20 03/12/31') --(wednesday -> returns 53)
Select datepart(ww,'20 04/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

[Erland Sommarskog]

Re: Wrong week numbers

Stig Andersen (winfinans@yaho o.dk) writes:[color=blue]
> Hi does anyone know how to make MS SQL server 2000 calculate the right
> week number around new year ?[/color]

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.)
[color=blue]
> 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:[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mike Sherrill]

Re: Wrong week numbers

On Tue, 21 Oct 2003 14:51:25 +0200, "Stig Andersen"
<winfinans@yaho o.dk> wrote:
[color=blue]
>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,'20 03/12/31') --(wednesday -> returns 53)
>Select datepart(ww,'20 04/1/1') --(thursday -> returns 1)[/color]

This is a symptom of a known bug. Q200299 might lead you to the
details. (Might not--memory fades.)
[color=blue]
>Does anyone know the cause or maybe a workaround to this problem ?[/color]

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

--
Mike Sherrill
Information Management Systems

[Erland Sommarskog]

Re: Wrong week numbers

Mike Sherrill (MSherrillnonon o@compuserve.co m) writes:[color=blue][color=green]
>>Select datepart(ww,'20 03/12/31') --(wednesday -> returns 53)
>>Select datepart(ww,'20 04/1/1') --(thursday -> returns 1)[/color]
>
> This is a symptom of a known bug. Q200299 might lead you to the
> details. (Might not--memory fades.)[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Stig Andersen]

Re: Wrong week numbers

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/node7.html#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" <sommar@algonet .se> wrote in message
news:Xns941D11A A8A1CYazorman@1 27.0.0.1...[color=blue]
> Mike Sherrill (MSherrillnonon o@compuserve.co m) writes:[color=green][color=darkred]
> >>Select datepart(ww,'20 03/12/31') --(wednesday -> returns 53)
> >>Select datepart(ww,'20 04/1/1') --(thursday -> returns 1)[/color]
> >
> > This is a symptom of a known bug. Q200299 might lead you to the
> > details. (Might not--memory fades.)[/color]
>
> 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, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]