A Simple Formula to Calculate the ISO Week Number

  • Jeff Moden

    SSC Guru

    Points: 994516

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • cppprogrammer

    Right there with Babe

    Points: 797

    Damnit! I went to vote 5 stars and the location of the 5th star changed so it hit 2?!? Can I change it?:unsure::blush:

  • Jeff Moden

    SSC Guru

    Points: 994516

    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?:unsure::blush:

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994516

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • cppprogrammer

    Right there with Babe

    Points: 797

    My only feedback is I'm a big fan of sticking to integer arithmetic where possible so a few neat tricks there!

  • Jeff Moden

    SSC Guru

    Points: 994516

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • SwePeso

    SSC-Dedicated

    Points: 39693

    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

    SSC-Dedicated

    Points: 39693

    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

    SSC Guru

    Points: 994516

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Andy Warren

    SSC Guru

    Points: 119676

    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.

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    I like the emphasis on thanking someone for writing a clever equation. Maybe it isn't as popular as y = mx + b, but that one doesn't solve for ISO Week Number - so the right tool for the job, eh?

    fwiw, thanks Jeff for another example of how to write an article taking us through the discovery/investigation process (ex: "I've left all of the other interim calculations in place just for comparison purposes and will continue to do so in all other examples that follow.")

  • ChrisM@Work

    SSC Guru

    Points: 186043

    How often we see "so how does that work, exactly?" when someone posts an up-to-date solution to their tricky problem. Try explaining this algorithm in the remaining 3 minutes of your lunch break!

    Thanks Jeff for taking the time to write - for a brilliant algorithm - an equally brilliant explanation. Bookmarked.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Johan Bijnens

    SSC Guru

    Points: 134254

    It took me some time to crawl back into working position after reading this finding.

    Completely astonished by the simplicity of the solution.

    Huge thank you for notifying and publishing another great help for the community.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Jeff Moden

    SSC Guru

    Points: 994516

    Andy Warren (4/8/2013)


    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.

    Thanks for the great feedback, Andy. Heh... yeah... I remember what it was like being new. Good thing I had backups so I could restore the things I deleted by mistake. Hopefully doing these types of experiments in TempDB will protect those that have not yet been burned by an experiment.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994516

    Mike Dougherty-384281 (4/8/2013)


    I like the emphasis on thanking someone for writing a clever equation. Maybe it isn't as popular as y = mx + b, but that one doesn't solve for ISO Week Number - so the right tool for the job, eh?

    fwiw, thanks Jeff for another example of how to write an article taking us through the discovery/investigation process (ex: "I've left all of the other interim calculations in place just for comparison purposes and will continue to do so in all other examples that follow.")

    Thanks, Mike. I sure do appreciate the feedback on this. Hopefully folks will go to the link I gave and give the inventor about a bazillion +1's because he sure does deserve it.

    I was absolutely amazed when I first tested the code to make sure it worked. I just had to tear it apart so I could understand it better. The code in the article is how I actually went through most of it. I'm getting too old to hold very many calculations in mid-air anymore. πŸ™‚

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply