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 Saturday, July 27, 2013 3:40 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 Yes, I did indeed. However, the time at my place was 3 am and time to go to bed...Here is a further reduced code (one less addition)`(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7, '17530104')) + 6) / 7`My blog about this is here.If you don't read Swedish well, use either Bing or Google. N 56°04'39.16"E 12°55'05.25"
Post #1478275
 Posted Saturday, July 27, 2013 10:25 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 38,966, Visits: 36,051
 SwePeso (7/27/2013)Yes, I did indeed. However, the time at my place was 3 am and time to go to bed...Here is a further reduced code (one less addition)`(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7, '17530104')) + 6) / 7`My blog about this is here.If you don't read Swedish well, use either Bing or Google.Thanks, Peter. I'll check out the formula. My question was based on the fact that we weren't receiving each other's emails in the past. --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." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Favorite Quotes:"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014Helpful Links:How to post code problemsHow to post performance problems
Post #1478295
 Posted Friday, October 23, 2015 7:47 AM
 SSC Journeyman Group: General Forum Members Last Login: 2 days ago @ 8:33 PM Points: 94, Visits: 383
 Now what happens when a government decides to skip a day...
Post #1730354
 Posted Friday, October 23, 2015 8:04 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 38,966, Visits: 36,051
 robert.sterbal 56890 (10/23/2015)Now what happens when a government decides to skip a day...Then it's no longer "ISO". What's your real question? --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." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Favorite Quotes:"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014Helpful Links:How to post code problemsHow to post performance problems
Post #1730360
 Posted Friday, October 23, 2015 8:13 AM
 SSC Journeyman Group: General Forum Members Last Login: 2 days ago @ 8:33 PM Points: 94, Visits: 383
 I like the way your formula skips the leap year problem.I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendar
Post #1730367
 Posted Friday, October 23, 2015 8:18 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 10:12 AM Points: 7,764, Visits: 16,378
 robert.sterbal 56890 (10/23/2015)...I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendarJeff - there's someone older than you on ssc “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1730370
 Posted Friday, October 23, 2015 8:21 AM
 SSC Journeyman Group: General Forum Members Last Login: 2 days ago @ 8:33 PM Points: 94, Visits: 383
 ChrisM@Work (10/23/2015)robert.sterbal 56890 (10/23/2015)...I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendarJeff - there's someone older than you on ssc I may or may not be older, but Jeff is definitely wiser and smarter!
Post #1730373
 Posted Friday, October 23, 2015 8:27 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, November 18, 2015 10:22 AM Points: 8, Visits: 307
 HiThanks for this simple and effective code.Is there a similar easy way to get the year of that week we are looking for? Or the "ISO-year"?Lets say I put in the date '2014-12-31' and get the ISO-WeekNumber = 1, I would also like to get the what year that weeknumber is for (in this case 2015). I guess I can try to find this out, but if you guys already have some really efficient code to do this, it would be appreciated.brGert
Post #1730378
 Posted Friday, October 23, 2015 8:49 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 38,966, Visits: 36,051
 robert.sterbal 56890 (10/23/2015)I like the way your formula skips the leap year problem.I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendarThat would be the reason why I don't use SQL Server for dates before 1753. Heh... Y2K was easy after that one. --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." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Favorite Quotes:"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014Helpful Links:How to post code problemsHow to post performance problems
Post #1730391
 Posted Friday, October 23, 2015 9:04 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 27, 2015 8:39 AM Points: 4, Visits: 27
 I don't believe it's a correct calculation for all situations. It doesn't look at the first day of a week. In Europe that is Monday, in other parts of the world this is Sunday.For instance Sunday 3 January 2016 for us in Europe is week 53 (of year 2015). In US this week 1.
Post #1730401

 Permissions