Get nth weekday date of every nth month in SQL Server

Question

I need to know the nth weekday date of every nth month in SQL Server. For example, I need all the date of 2nd Monday from January 1, 2015 to December 30, 2015. it is to get weekday date. like first monday or 3rd tuesday of every 3rd month between a range of date.

This was marked as a duplicate, but that question was about days of the month, not week days. — Gordon Linoff, Dec 09 '15 at 12:45

score 1 · Answer 1 · edited May 23 '17 at 11:59

Some time ago I answered another question here: https://stackoverflow.com/a/32474751/5089204

In short: Create a table with running numbers (-> Tally Table). Such a table is very handsome in many cases - one should have something like this anyway... It does not need much but it will help you for sure...

Out of this table you can get all your needed data with simple 'SELECT'. Maybe you'd have to add more columns and fill them to get your data easily. But this shouldn't be a problem...

This will be the result (running deep into the year 2173)

Number  CalendarDate    CalendarYear    CalendarMonth   CalendarDay CalendarWeek    CalendarYearDay CalendarWeekDay
0       1900-01-01      1900            1               1           1               1               1
1       1900-01-02      1900            1               2           1               2               2
2       1900-01-03      1900            1               3           1               3               3
3       1900-01-04      1900            1               4           1               4               4
4       1900-01-05      1900            1               5           1               5               5
5       1900-01-06      1900            1               6           1               6               6

For performance it will be good to set indexes!

score 0 · Accepted Answer · answered Dec 09 '15 at 13:38

This should e.g, return all 3rd Tuesdays:

;WITH AllDates AS (
    SELECT CAST('2015' + Mnth + Dy AS date) AS Dt
    FROM (
        SELECT '01' AS Mnth UNION SELECT '02' UNION SELECT '03' UNION SELECT '04' 
        UNION SELECT '05' UNION SELECT '06' UNION SELECT '07' UNION SELECT '08' 
        UNION SELECT '09' UNION SELECT '10' UNION SELECT '11' UNION SELECT '12'
    ) AS Mnths
    CROSS JOIN (
        SELECT '01' AS Dy UNION SELECT '02' UNION SELECT '03' UNION SELECT '04' 
        UNION SELECT '05' UNION SELECT '06' UNION SELECT '07' UNION SELECT '08' 
        UNION SELECT '09' UNION SELECT '10' UNION SELECT '11' UNION SELECT '12'
        UNION SELECT '13' UNION SELECT '14' UNION SELECT '15' UNION SELECT '16'
        UNION SELECT '17' UNION SELECT '18' UNION SELECT '19' UNION SELECT '20'
        UNION SELECT '21' UNION SELECT '22' UNION SELECT '23' UNION SELECT '24'
        UNION SELECT '25' UNION SELECT '26' UNION SELECT '27' UNION SELECT '28'
        UNION SELECT '29' UNION SELECT '30' UNION SELECT '31'
    ) AS Dys
    WHERE ISDATE('2015' + Mnth + Dy) = 1
), AllDatesWithInfo AS (
    SELECT Dt, DATENAME(weekday, Dt) AS WeekDay, ROW_NUMBER() OVER (PARTITION BY YEAR(Dt), MONTH(Dt), DATENAME(weekday, Dt) ORDER BY Dt) AS NumberOfThisWeekDayInMonth
    FROM AllDates
)
SELECT *
FROM AllDatesWithInfo
WHERE WeekDay = 'Tuesday'
AND NumberOfThisWeekDayInMonth = 3
ORDER BY Dt

If anyone knows a better way to get the "AllDates" virtual table, I'd love to hear it... — André Kops, Dec 09 '15 at 13:40
That's indeed less code and it will generate more possibilities. I will remember that technique, thanks. — André Kops, Dec 09 '15 at 16:05

Get nth weekday date of every nth month in SQL Server

2 Answers2