SQL: Calculating day of the week across a range of years in T-SQL

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;

WITH Years
AS
(
    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
)   
SELECT YearNumber,
       DATENAME
       (
           weekday, 
           DATEFROMPARTS(YearNumber, @MonthNumber, @DayNumber)
       ) AS [WeekDay]
FROM Years
ORDER BY YearNumber;

The output is shown in the main image above.

2021-02-25