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;

DECLARE @DayNumber int = 20; -- Target day

DECLARE @MonthNumber int = 11; -- Target month

DECLARE @FromYear int = 2021;

DECLARE @ToYear int = 2030;

WITH Years

(

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.

Leave a Reply Cancel reply