How to get the Monday position of a month in T-SQL-CodePudding

For example consider the April 2023. In this year the Monday dates are 3, 10, 17, and 24. Now I want to get the date 10 position of April.

My target result is: April 10 = second or 2

CodePudding user response：

SELECT (DAY('2023-04-10') - 1) / 7 1

CodePudding user response：

See Position

WITH CTE_DATES 
AS(
    SELECT CAST(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) -1, 0) AS DATE) AS [DATE] -- start previous month

    UNION ALL

    SELECT CAST(DATEADD(DD, 1, [DATE])  AS DATE)
    FROM [CTE_DATES]
    WHERE [DATE] < DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())   0, 0)) -- end previous month
)

SELECT 
     [date] 
    ,DATENAME(WEEKDAY, [date]) AS 'Weekday'
    ,DATEPART(WEEKDAY, [date]) AS 'Day'
    ,ROW_NUMBER() OVER (ORDER BY [date] ASC) AS 'POSITION'
FROM [CTE_DATES]
WHERE DATEPART(WEEKDAY, [date]) IN (1)