SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple Formula to Calculate the ISO Week Number


A Simple Formula to Calculate the ISO Week Number

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
Comments posted to this topic are about the item A Simple Formula to Calculate the ISO Week Number

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cppprogrammer
cppprogrammer
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 130
Damnit! I went to vote 5 stars and the location of the 5th star changed so it hit 2?!? Can I change it?UnsureBlush
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
cppprogrammer (4/7/2013)
Damnit! I went to vote 5 stars and the location of the 5th star changed so it hit 2?!? Can I change it?UnsureBlush


Heh... no. I don't know of anyway to change it. Your feedback is better than the stars anyway. Thanks for the read and the thought. I appreciate it. :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
You can tell a lot about the stars this early in the game. There was an accidental "2", and then a "5" for a total of 7/2 stars. Now the average is back down to "2" which means a couple of folks gave the article some really low marks. The problem is, they didn't take the time to explain why in this discussion, so I have to guess why that may be and don't actually learn anything from them.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cppprogrammer
cppprogrammer
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 130
My only feedback is I'm a big fan of sticking to integer arithmetic where possible so a few neat tricks there!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
cppprogrammer (4/8/2013)
My only feedback is I'm a big fan of sticking to integer arithmetic where possible so a few neat tricks there!


Me too! I knew it was going to be lightning quick when I first saw it just because of the Integer Math in it. "t-clausen.dk" did a heck of a job on this one. Here's his link again (look for his second post on the thread). Let's get everyone to stop over there and lets see if we can drive his post over the 5,000 mark. The man definitely deserves it for this one!
http://stackoverflow.com/questions/7330711/isoweek-in-sql-server-2005

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4021 Visits: 3433
This is one excellent algorithm to get the ISO week number.
However doing this by using 17530101 instead of 19000101 throws an error


Arithmetic overflow error converting expression to data type datetime.



It turns out the highest integer value "DATEPART(DAYOFYEAR" can accept 2958463. Which is 8100 years, which accidentally is the number of years between 1900 and 9999 and number of days between 19000101 and 99991231. My guess is that a developer over at Microsoft has hardwired this upper limit.


N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4021 Visits: 3433
However, the fix is really simple. Since all we need in an anchor date of a monday, write this case statement.

--===== Test the new formula ============================================================
DECLARE @BitBucket INT;
SELECT @BitBucket = CASE
WHEN Date < '19000101' THEN DATEPART(dy,DATEDIFF(dd,'17530101',Date)/7*7+3)+6
ELSE DATEPART(dy,DATEDIFF(dd,'19000101',Date)/7*7+3)+6
end / 7
FROM #TestTable;
GO 5




N 56°04'39.16"
E 12°55'05.25"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
SwePeso (4/8/2013)
This is one excellent algorithm to get the ISO week number.
However doing this by using 17530101 instead of 19000101 throws an error


Arithmetic overflow error converting expression to data type datetime.



It turns out the highest integer value "DATEPART(DAYOFYEAR" can accept 2958463. Which is 8100 years, which accidentally is the number of years between 1900 and 9999 and number of days between 19000101 and 99991231. My guess is that a developer over at Microsoft has hardwired this upper limit.


Too funny and brilliant observation, Peter. Your CASE statement fix works great especially since I would imagine that most folks would encapsulate the formula in one form or another of a function.

Your fix for the earlier date does have me thinking about Integer Math and conversions, though. I don't know why, but I've never tested to see if the use of Date Serial Numbers such as 0 ('19000101') and -53690 ('17530101') are faster than their string counter parts as we use them in some of the date functions. We do know that CAST and CONVERT to strings and back again is slower but I've never tested the implicit conversions. I'll give it a whirl when I get home from work tonight.

Thanks for stopping by, Peter. Haven't seen you around much and it's a real pleasure to see that fabulous mind at work here, again.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11625 Visits: 2730
Jeff, that is a very nice write up and a very nice job of giving credit where credit is due. The code is interesting, the explanation is just first class, I appreciate the little details like running it in Tempdb to make it easier for someone new.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search