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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.