error in calcualting week nr over DATEPART

hello,
i have the problem that the w nr calcualted from the actual date in not
localized...
in germany for example - from 21.11.2009 till 27.11.2005 there is the w
nr:47
if i execute in the queryanalyser
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
i get the value 48 - instead of 47
any suggestion how to correct this?
thanksMaybe this will solve your problem:
set datefirst 6
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||Don't use DATEPART to calculate w numbers, SQL Server doesn't follow the
ISO standard for this.
Use the ISOW function found in Books Online, of use a calendar table (you
can find an article on
this on www.aspfaq.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||yes, this solved my problem.
thanks Raymond
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||hello Raymond,
sorry, but i have still problems
set datefirst 6
go
Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
also the problem exist for
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
any ideas?
thanks
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||I had a feeling that it may not solve all your problems. :-(
Look at the Tibor's answer.
Here is the direct link to the article he suggested.
http://www.aspfaq.com/show.asp?id=2519
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:49D92FAA-54BE-4791-8C3B-13F3AA8D37C8@.microsoft.com...
> hello Raymond,
> sorry, but i have still problems
> set datefirst 6
> go
> Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
> Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
> also the problem exist for
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
> 1)
> any ideas?
> thanks
>
>
> "Raymond D'Anjou" wrote:
>|||hello Tibor,
yes the ISOW function solved my problem with calculating the w nr.
Any idea how to solve my problem with dw...
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
thanks
"Tibor Karaszi" wrote:

> Don't use DATEPART to calculate w numbers, SQL Server doesn't follow th
e ISO standard for this.
> Use the ISOW function found in Books Online, of use a calendar table (y
ou can find an article on
> this on www.aspfaq.com).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||Always use a language neutral datetime format: http://www.karaszi.com/SQLServer/in...o_datetime.asp.
Anyhow, make sure you have proper SET DATEFIRST:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:9EB31742-EA64-4148-994E-531C3B730DA8@.microsoft.com...
> hello Tibor,
> yes the ISOW function solved my problem with calculating the w nr.
> Any idea how to solve my problem with dw...
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
1)
> thanks
> "Tibor Karaszi" wrote:
>