|
I'm just curious about some specifics within the datepart function, specificly when extracting the week number. I'm currently selecting a sequence of weeks to use on a join later in a procedure. I've noticed however when going between years, the week numbers almost appeared to be labeled wrong. I was getting week 54 often and thought it was a bug, but after going into the documentation on datepart it appears that it can return values up to 54. I'm wondering wondering why it does this, is it a sybase specific thing? I know years typically can have 53 weeks, but I'm pretty sure 54 is impossible, so just wondering why it is using that range to begin with. |
|
ISO 8601 specifies that a year has 52 or 53 weeks. So if you care for ISO, week 54 should be treated as week 1 of the following year. 1
I think you just need to decide what type of week you want. The datepart function gives you two choices: Week and Calweekofyear. Week behaves as Breck noted above and can give a 54 week year in specific circumstances. Calweekofyear says it follows ISO 8601, so is limited to 1-53.
(25 Jul '22, 14:52)
Justin Willey
1
Not all cultures follow ISO specs, most do not use ISO date format either, I suspect... :)
(26 Jul '22, 05:04)
Volker Barth
Replies hidden
1
Indeed - UK fiscal year is 6th April - 5th April, thanks to Lady Day (the Annunciation) being shifted by 11 days with the calendar change in 1752 and HMRC not wanting to lose 11 days revenue that year! So week numbers go all over the place depending on which day of the week the year the 6th falls.
(26 Jul '22, 05:20)
Justin Willey
|
Perhaps a week is a week, not just the number of days divided by seven :)
Specifying Date Parts
Thank you for your reply! After doing a little bit more digging I've realized that week 53 and 1 are treated more or less as the same week. All days that don't fit within the year will carry over into week 1.