Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sprinjee
Starting Member
42 Posts |
 Posted - 2006-10-25 : 06:18:46
|
| When using the syntax: datepart(ww, somedate)the returned weeknumber is correct 99 out of a 100 times after setting:SET DATEFIRST 7However when returning the week: datepart(ww, '12-31-2000 00:00:00.000') the week returned by SQL = 54, according to my calendar it should be week 1. '12-30-2000 00:00:00.000' return 53, this should be 52Furthermore, '01-01-2000 00:00:00.000' returns 1 while this should be 52.However, '01-01-2006 00:00:00.000' returns 1 which is correct.I consider the the first calendar week of the year is the one that includes the first wednesday of that year.Does anyone know how to resolve this? Or perhaps has a function that returns the correct weeknumber? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-25 : 06:26:03
|
It's not wrong. It is just how SQL Server defination week no is different from yours.from BOLquote:
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.
If you want to use the ISO Week no, refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510Or build your own calendar table and define your own week no.
KH |
 |
|
|
|
|
|
|
|
Weeknumber in SQL