I had a question from Dale Kerr the other day about whether we had a tool in our SDU Tools collection, that calculated the day of the week (i.e. Tuesday, Thursday) for a given day of the year, across a range of years.
We don't have a specific tool for that, but the a CTE returning the list of years makes it easy.
(UPDATE: SDU Tools version 21 will have WeekdayAcrossYears that implements this)
Here's an example:
DECLARE @DayNumber int = 20; -- Target day
DECLARE @MonthNumber int = 11; -- Target month
DECLARE @FromYear int = 2021;
DECLARE @ToYear int = 2030;
SELECT TOP(@ToYear - @FromYear + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT 1))
+ @FromYear - 1 AS YearNumber
FROM sys.all_columns AS ac1
CROSS JOIN sys.all_columns AS ac2
DATEFROMPARTS(YearNumber, @MonthNumber, @DayNumber)
) AS [WeekDay]
ORDER BY YearNumber;
The output is shown in the main image above.