How to get 2nd and 4th Monday of the month?

1

Hi. I'm new to this forum and sql.

How do I get 2nd and 4th Monday of the month via SQL?

2

You can use a calendar table and maintain the required info...that can be used for many years.

Alternatively you can use this

DECLARE @Year int = 2023;
DECLARE @Month int = 3;
WITH Dates AS (
    SELECT DATEFROMPARTS(@Year, @Month, 1) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date)
    FROM Dates
    WHERE MONTH(DATEADD(day, 1, Date)) = @Month
)
SELECT Date
FROM (
    SELECT Date, ROW_NUMBER() OVER (ORDER BY Date) AS RowNumber
    FROM Dates
    WHERE DATENAME(weekday, Date) = 'Monday'
) AS Mondays
WHERE RowNumber IN (2, 4);
3

Cool. Thanks.

4

Much more efficient is to just use date math to calculate the dates.

For example, to get the 2nd and 4th Mondays for the current month:


;WITH cte_calc_second_Monday AS (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, day14) % 7, day14) AS second_Monday
    FROM ( VALUES(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 14)) ) AS days(day14)
)
SELECT second_Monday, DATEADD(DAY, 14, second_Monday) AS fourth_Monday
FROM cte_calc_second_Monday

Once you've done the full calc to get the 2nd Monday, naturally all you have to do is add 14 to get the 4th Monday.