Get week day name from a given month, day and year individually in SQL Server

Get Weekday Name from Month, Day, and Year in SQL Server

Learn how to retrieve the full weekday name (e.g., 'Monday', 'Tuesday') in SQL Server when provided with separate month, day, and year values. This article covers various functions and best practices.

Working with dates in SQL Server is a common task, and often you'll need to extract specific components, such as the day of the week. While SQL Server provides robust date and time functions, getting the weekday name from individual month, day, and year components requires a specific approach. This article will guide you through the process, explaining the functions involved and providing practical examples.

Understanding SQL Server Date Functions

SQL Server offers several built-in functions for date and time manipulation. To get the weekday name, the primary challenge is to first construct a valid DATE or DATETIME object from the separate month, day, and year integers. Once you have a valid date, you can then use functions like DATENAME or FORMAT to extract the weekday name.

flowchart TD
    A[Start with Month, Day, Year] --> B{Construct Date String}
    B --> C[Convert String to DATE/DATETIME]
    C --> D{Extract Weekday Name}
    D --> E[Result: Weekday Name]

Process flow to get weekday name from individual date components.

Method 1: Using DATEFROMPARTS and DATENAME

The DATEFROMPARTS function is the most straightforward way to construct a DATE value from integer year, month, and day components. Once the date is formed, the DATENAME function can be used with the weekday datepart to return the full name of the day of the week.

DECLARE @Year INT = 2023;
DECLARE @Month INT = 10;
DECLARE @Day INT = 26;

SELECT DATENAME(weekday, DATEFROMPARTS(@Year, @Month, @Day)) AS WeekdayName;

Using DATEFROMPARTS and DATENAME to get the weekday name.

Method 2: Using CONVERT and FORMAT (SQL Server 2012+)

Another modern approach involves constructing a date string and then using the FORMAT function. The FORMAT function provides powerful formatting options for date and time values, including various weekday name formats. This method is also available from SQL Server 2012 onwards.

DECLARE @Year INT = 2023;
DECLARE @Month INT = 10;
DECLARE @Day INT = 26;

SELECT FORMAT(CONVERT(DATE, CAST(@Year AS VARCHAR(4)) + '-' + 
                      CAST(@Month AS VARCHAR(2)) + '-' + 
                      CAST(@Day AS VARCHAR(2))), 'dddd') AS WeekdayName;

Using CONVERT and FORMAT to get the full weekday name.

Method 3: For Older SQL Server Versions (Pre-2012)

If you are working with SQL Server 2008 R2 or earlier, DATEFROMPARTS and FORMAT are not available. In such cases, you'll need to concatenate the year, month, and day into a valid date string and then CONVERT it to a DATETIME or DATE type before using DATENAME.

DECLARE @Year INT = 2010;
DECLARE @Month INT = 10;
DECLARE @Day INT = 26;

SELECT DATENAME(weekday, CONVERT(DATETIME, 
    CAST(@Year AS VARCHAR(4)) + '-' + 
    CAST(@Month AS VARCHAR(2)) + '-' + 
    CAST(@Day AS VARCHAR(2)))) AS WeekdayName;

Getting weekday name in older SQL Server versions.