A Simple Formula to Calculate the ISO Week Number

Question

A Simple Formula to Calculate the ISO Week Number

Viewing 5 posts - 76 through 79 (of 79 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

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

michael.zanovec@la.gov Newbie Points: 2 More actions · Answer 4

Been searching for a non-Power Query, non-DAX way to do this for days. Thank you!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

michael.zanovec@la.gov wrote:

Been searching for a non-Power Query, non-DAX way to do this for days. Thank you!

Thank you for the feedback.

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