2

Does anyone have any idea how to get the week number of the month with 2 conditions: Sunday starts the week according to our ISO standard and the week must belong to the month it starts in. I was able to get this working using code that assumes Monday starts the week and setting DATEFIRST to 1 but now I have to do it without setting the DATEFIRST so I'll have to revert with Sunday as the start in mind.

declare @created_dt datetime = '2014-10-25'

select datepart(day, datediff(day, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, @created_dt),
           CAST(@created_dt AS DATE)))/7 * 7)/7 + 1

I took a look at the link here and the solution is not the same: TSQL Calculate week number of the month

That solution for example will list Oct 19, 2014 as a day in the 4th week of the month of October. My solution will need to return 3 as the answer because each week will belong to the month it starts in e.g Sept 29 to October 5 is a week belonging to September. Oct 19 would therefore be in the 3rd week of October following my requirement

Community
  • 1
  • 1
Opeyemi Ilupeju
  • 21
  • 3
  • Another excellent case for creating a calendar table... – N West Nov 05 '14 at 16:19
  • 1
    @MikeMiller i took a look at that. That solution for example will list Oct 19, 2014 as a day in the 4th week of the month of October. My solution will need to return 3 as the answer because each week will belong to the month it starts in e.g Sept 29 to October 5 is a week belonging to September. Oct 19 would therefore be in the 3rd week of October following my requirement – Opeyemi Ilupeju Nov 05 '14 at 16:33

0 Answers0