In T-SQL, I need to find the number of ISO-weeks in a month.
According to client specifications, a week belongs to the month that has the sunday of the week number.
For instance, week 5 2024 belongs to February, not January, because Sunday Feb 4th is in February.
Context: I need to use it to find the average staffing needs for a given month, based on staffing needs specified for each week in that month, as in this simplified pseudo code:
WeeklyStaffingNeedsTotal / GetNumberOfIsoWeeksInMonth(year, month) AS MonthlyStaffingNeed
An ISO week runs from Monday to Sunday. To count the number of ISO weeks that end on a Sunday contained in a given month, you really just need to count Sundays in that month. If you can identify the last Sunday of the month, the day number will tell you the total number of Sundays in that month. If the last Sunday falls on the 28th or earlier, there are four Sundays and four ISO weeks that end in that month. If the last Sunday falls on the 29th or later, there are five Sundays and five ISO weeks that end in that month. This can also be calculated as (DAY(last-Sunday-of-month + 6) / 7.
To apply the above logic, we need to first calculate the last Sunday of the month. We can obtain the last day of a month using the EOMONTH() function. We can then use the DATE_BUCKET() function (new in SQL Server 2022) to adjust that date to the last Sunday using the week option and an origin date representing some arbitrary reference Sunday.
In summary, we need to:
EOMONTH(date).DATE_BUCKET(week, 1, end-of-month, reference-sunday)(DAY(last-Sunday) + 6) / 7The combined calculation would be:
DECLARE @OriginSunday DATE = '20240107' -- 7 January 2024 (any Sunday will do)
DECLARE @Date DATE = '20240301' -- March 2024
SELECT (DAY(DATE_BUCKET(week, 1, EOMONTH(@Date), @OriginSunday)) + 6) / 7 AS NumSundays
The result for the above is 5.
For all of 2024, the calculations would yield:
| Dt | EndOfMonth | LastSunday | SundaysInMonth |
|---|---|---|---|
| 2024-01-01 | 2024-01-31 | 2024-01-28 | 4 |
| 2024-02-01 | 2024-02-29 | 2024-02-25 | 4 |
| 2024-03-01 | 2024-03-31 | 2024-03-31 | 5 |
| 2024-04-01 | 2024-04-30 | 2024-04-28 | 4 |
| 2024-05-01 | 2024-05-31 | 2024-05-26 | 4 |
| 2024-06-01 | 2024-06-30 | 2024-06-30 | 5 |
| 2024-07-01 | 2024-07-31 | 2024-07-28 | 4 |
| 2024-08-01 | 2024-08-31 | 2024-08-25 | 4 |
| 2024-09-01 | 2024-09-30 | 2024-09-29 | 5 |
| 2024-10-01 | 2024-10-31 | 2024-10-27 | 4 |
| 2024-11-01 | 2024-11-30 | 2024-11-24 | 4 |
| 2024-12-01 | 2024-12-31 | 2024-12-29 | 5 |
See this db<>fiddle for a demo that includes and entire 28-year calendar cycle.