Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden A Simple Formula to Calculate the ISO Week Number RE: A Simple Formula to Calculate the ISO Week Number

  • July 26, 2013 at 12:01 am

    #1636042

    I still have some testing to do but it appears that converting the DATEDIFF back to a DATETIME using DATEADD instead of mistakenly allowing DATEPART to do it fixes all the problems. Here's the corrected formula. I'm using the base date of '1753' in this formula and it appears to be working correctly for all dates from 1753-01-01 through 9999-12-31. I still need to test it with the ranges of the "new" DATE datatype.

    (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'1753',Date)/7*7+3 ,'1753'))+6)/7

    --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.

    Change is inevitable... Change for the better is not.

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