Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 A Simple Formula to Calculate the ISO Week Number Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, April 7, 2013 10:03 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:10 AM Points: 42,046, Visits: 39,431
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1439678
 Posted Sunday, April 7, 2013 10:05 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, September 22, 2015 6:55 PM Points: 47, 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?
Post #1439679
 Posted Sunday, April 7, 2013 11:29 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:10 AM Points: 42,046, Visits: 39,431
 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?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." Helpful Links:How to post code problemsHow to post performance problems
Post #1439688
 Posted Sunday, April 7, 2013 11:58 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:10 AM Points: 42,046, Visits: 39,431
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1439689
 Posted Monday, April 8, 2013 12:02 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, September 22, 2015 6:55 PM Points: 47, Visits: 130
 My only feedback is I'm a big fan of sticking to integer arithmetic where possible so a few neat tricks there!
Post #1439691
 Posted Monday, April 8, 2013 12:25 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:10 AM Points: 42,046, Visits: 39,431
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1439692
 Posted Monday, April 8, 2013 2:39 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 This is one excellent algorithm to get the ISO week number.However doing this by using 17530101 instead of 19000101 throws an errorArithmetic 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"
Post #1439710
 Posted Monday, April 8, 2013 2:45 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 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"
Post #1439711
 Posted Monday, April 8, 2013 5:29 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:10 AM Points: 42,046, Visits: 39,431
 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 errorArithmetic 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1439757
 Posted Monday, April 8, 2013 7:20 AM
 SSCertifiable Group: Moderators Last Login: 2 days ago @ 6:27 AM Points: 7,061, Visits: 2,660
 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.
Post #1439814

 Permissions