## A Simple Formula to Calculate the ISO Week Number

 Author Message SwePeso SSCrazy Eights Group: General Forum Members Points: 9613 Visits: 3433 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" Jeff Moden SSC Guru Group: General Forum Members Points: 214854 Visits: 41979 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 ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs robert.sterbal 56890 SSCrazy Group: General Forum Members Points: 2402 Visits: 1236 Now what happens when a government decides to skip a day... Jeff Moden SSC Guru Group: General Forum Members Points: 214854 Visits: 41979 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 ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs robert.sterbal 56890 SSCrazy Group: General Forum Members Points: 2402 Visits: 1236 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 ChrisM@Work SSC-Forever Group: General Forum Members Points: 41405 Visits: 20005 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 robert.sterbal 56890 SSCrazy Group: General Forum Members Points: 2402 Visits: 1236 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! Beugen Grasshopper Group: General Forum Members Points: 16 Visits: 341 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 Jeff Moden SSC Guru Group: General Forum Members Points: 214854 Visits: 41979 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. :-P --Jeff ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs marc.corbeel SSC Veteran Group: General Forum Members Points: 249 Visits: 96 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.