A Simple Formula to Calculate the ISO Week Number

  • SwePeso wrote:

    DATEPART(ISO_WEEK, @thedate) ???

    Afaik this helps going in the other direction, from @thedate to iso week.  What about iso week, iso month, iso year to calendar date?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Heh... I'm no longer surprised by such things.  For example, the came out with EOMonth, which has the very problem we're talking about.  They didn't come out with an SOMonth or BOMonth function, though.  ISOWK has been out for more than a decade and they still don't have a function to convert to a date range.  And the things they did to the newer date type (DATE, TIME, DATETIME2) are just horrendous.  Then you look at things like the train-wrecks they call PIVOT,  FORMAT,  STRING_SPLIT, SSMS loss of functionality (and it continues), and, going way back, the removal of things like sp_WebTask, SP's and CU's  that blow things up (Online Clustered Index Rebuild cause corruption in 2012 prior to sp2 IIRC, for example), improvements like the Cardinality Estimator (killed me),  making it so I can't disable the equivalent of TF 1118 on TempDB, and the performance train-wreck of 2017 RTM (and many others),  etc, etc, you'll understand why I've become a little numb to it all.

    My only saving grace in some of this is to constantly repeat "A'm workin' for ah livin' so A'm takin' what dey givin'". 😀  I'd retire but I'm having too much fun watching stuff like this happen for real.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • -- Local helper variables
    DECLARE @StartOfWeek DATETIME = DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, '18940108', DATEFROMPARTS(@Year, 1, 4)) / 7 * 7, '18940101'),
    @EndOfWeek DATETIME = DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, '18940108', DATEFROMPARTS(@Year, 1, 4)) / 7 * 7, '18940107');

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 3 posts - 76 through 78 (of 78 total)

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