SDU Tools: Weekday Across Years in SQL Server T-SQL

Another request that I received a while back, for a new function to our free SDU Tools for developers and DBAs, was to be able to find the day of the week, for the same day and month, over a range of years. A simple example would be to find what day Christmas will be each year for the next ten years. So we've added a new function WeekdayAcrossYears.

It takes four parameters:

@DayNumber int – day number in the target month
@MonthNumber int – target month number
@FromYear int – starting year
@ToYear int – ending year

The function returns a rowset with YearNumber and WeekDay (in English).

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.WeekdayAcrossYears
(
    @DayNumber int,
    @MonthNumber int,
    @FromYear int,
    @ToYear int
)
RETURNS TABLE
AS
-- Function:      Returns a table of days of the week for a given day 
--                over a set of years
-- Parameters:    @DayNumber => day number of the target month
--                @MonthNumber => target month number
--                @FromYear => starting year number
--                @ToYear => ending year number
-- Action:        For a particular day and month, returns the day of 
--                the week for a range of years
-- Return:        Rowset with YearNumber as an integer, and WeekDay 
--                as a string
-- Refer to this video: https://youtu.be/k4wY1isY1G0
--
-- Test examples: 
/*

SELECT * FROM SDU_Tools.WeekdayAcrossYears(20, 11, 2021, 2030);

*/
RETURN 
(
    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
);
GO

CREATE OR ALTER FUNCTION SDU_Tools.WeekdayAcrossYears

(

@DayNumber int,

@MonthNumber int,

@FromYear int,

@ToYear int

)

RETURNS TABLE

-- Function: Returns a table of days of the week for a given day

-- over a set of years

-- Parameters: @DayNumber => day number of the target month

-- @MonthNumber => target month number

-- @FromYear => starting year number

-- @ToYear => ending year number

-- Action: For a particular day and month, returns the day of

-- the week for a range of years

-- Return: Rowset with YearNumber as an integer, and WeekDay

-- as a string

-- Refer to this video: https://youtu.be/k4wY1isY1G0

-- Test examples:

SELECT * FROM SDU_Tools.WeekdayAcrossYears(20, 11, 2021, 2030);

RETURN

(

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

);

Find out more

Latest version of the code

Leave a Reply Cancel reply