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.

asked 22 Jul, 13:31

BrandonS's gravatar image

BrandonS
1614
accept rate: 0%

2

Perhaps a week is a week, not just the number of days divided by seven :)

Specifying Date Parts

1-54. Weeks begin on Sunday. A 54-week year occurs in leap years that start on a Saturday. Week is not subject to the first_day_of_week setting.
(22 Jul, 13:43) Breck Carter
Replies hidden

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.

(22 Jul, 14:21) BrandonS

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.

permanent link

answered 25 Jul, 05:31

tedfroehlich's gravatar image

tedfroehlich
2302516
accept rate: 20%

edited 26 Jul, 05:08

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, 14:52) Justin Willey
1

Not all cultures follow ISO specs, most do not use ISO date format either, I suspect... :)

(26 Jul, 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, 05:20) Justin Willey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24

question asked: 22 Jul, 13:31

question was seen: 224 times

last updated: 26 Jul, 05:22