Function to find the number of ISO-weeks in a month

Question

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

Answer 1

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:

1. Calculate the last day of the month using EOMONTH(date).
2. Calculate the last Sunday using DATE_BUCKET(week, 1, end-of-month, reference-sunday)
3. Calculate the number of Sundays in the month using (DAY(last-Sunday) + 6) / 7

The 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.

Answer 2

NOT A FULL ANSWER (question is not clear) i am no clear what you need and how to filter, but this is a script to have a list of dates in year with weekname , day and day name (just in case) so would be your starting point

DECLARE @MinDate DATETIME = '2024-1-1 00:02:00.000',
    @MaxDate DATETIME = '2024-12-31 00:00:00.000';
Select * from (
    select 
        dates, 
        WeekNumber = datepart(wk, dates),
        DayNumber = datepart(day, dates),
        DayName = FORMAT(CAST(dates AS DATE), 'ddd')
    from (
        SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Dates = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
        FROM sys.all_objects a CROSS JOIN sys.all_objects b
    ) c
) w

and result would be

| dates                   | WeekNumber | DayNumber | DayName |
| ----------------------- | ---------- | --------- | ------- |
| 2024-01-01 00:02:00.000 | 1          |    1      | Mon     |
| 2024-01-02 00:02:00.000 | 1          |    2      | Tue     |
| 2024-01-03 00:02:00.000 | 1          |    3      | Wed     |