计算当前日期在当月的周数

Question

我查询当前日期在当月的周数时发现03-31在当月是第四周且是最后一周，04-01也在03-31的那一周中，但是查询04-01的周数始终是查出0，我想要的结果是查询04-01是3月的第四周，04-02是4月的第一周，应该用SqlServer如何实现？

select CASE WHEN'2023-03-31 00:00:00.000' < DATEADD(DAY, (7 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-03-31 00:00:00.000'), 0)) + 1) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-03-31 00:00:00.000'), 0)) THEN 0
ELSE DATEDIFF(WEEK, DATEADD(DAY, (7 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-03-31 00:00:00.000'), 0)) + 1) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-03-31 00:00:00.000'), 0)), '2023-03-31 00:00:00.000') + 1
END AS WeekOfMonth

Answer 1

DECLARE @currentDate DATETIME = '2023-04-01'
DECLARE @firstDayOfMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, @currentDate), 0)
 
SELECT 
    CASE 
        WHEN @firstDayOfMonth > @currentDate
            THEN 0
        ELSE DATEDIFF(WEEK, @firstDayOfMonth, @currentDate) + 1
    END AS WeekOfMonth