SQL Server Weekday From Date

In this comprehensive article, I’ll walk you through multiple methods to get weekdays from dates in SQL Server, explain their performance implications with multiple examples.

SQL Server Weekday From Date

Before diving into the technical details, let’s understand why extracting weekday information is so valuable:

• Business intelligence: Analyzing day-of-week patterns.
• Resource scheduling: Optimizing staffing levels based on historical weekday demand
• Financial reporting: Many financial calculations require business day information
• Automated notifications: Triggering specific actions on particular weekdays
• Data visualization: Creating intuitive dashboards with weekday breakdowns

Now, let’s explore the various methods to get weekday from date in SQL Server.

Approach 1: Using DATENAME() Function for Weekday Names

The most straightforward method to get the weekday name from a date in SQL Server is to use the DATENAME() function with the weekday datepart.

SELECT DATENAME(weekday, '2023-07-04') AS WeekdayName;

After executing the above query, it returned the expected output of Wednesday, as shown in the screenshot below.

The DATENAME function is an excellent choice when you need the actual name of the weekday rather than a numeric representation.

You can also use the abbreviated form dw instead of weekday using the below query:

SELECT DATENAME(dw, '2025-07-09') AS WeekdayName;

I got the expected output as Wednesday as shown in the screenshot below.

This function will return weekday names according to your SQL Server’s language setting.

Approach 2: Using the FORMAT() Function (SQL Server 2012 and Later)

If you’re working with SQL Server 2012 or later, the FORMAT() function provides a clean way to extract weekday information:

Example 1

We can execute the following query to get the full weekday name.

SELECT FORMAT(CAST('2025-07-08' AS date), 'dddd') AS WeekdayName;

I got the expected output as Wednesday as shown in the screenshot below.

Example 2

We can execute the following query to get the abbreviated weekday name.

SELECT FORMAT(CAST('2023-07-09' AS date), 'ddd') AS WeekdayAbbreviation;

I got the expected output as Sunday as shown in the screenshot below.

The FORMAT() Function is handy when you need precise control over the output format. However, it’s worth noting that FORMAT() is generally slower than DATENAME() for large datasets.

Approach 3: Using DATEPART() Function for Weekday Numbers

When you need a numeric representation of the weekday, the DATEPART() function is the way to go. In SQL Server, the week begins with Sunday (1) and ends with Saturday (7) by default.

SELECT DATEPART(weekday, '2023-07-09') AS WeekdayNumber;

The above query returns: 3 (representing Wednesday) as expected as shown below.

Extracting the day of the week is a common requirement while working with dates in SQL Server, and DATEPART offers a numeric approach that’s ideal for calculations and comparisons.

The numerical representation is particularly useful when:

• Sorting data by weekday
• Creating conditional logic based on weekdays
• Grouping data for analysis
• Building custom calendars

Approach 4: Using @@DATEFIRST to Control Week Start Day

One important consideration when working with weekdays in SQL Server is that the numbering system can vary based on your @@DATEFIRST settings. This system variable determines which day is considered the first day of the week.

First, we can execute the following query to check the current settings.

SELECT @@DATEFIRST;

Now, set Monday as the first day of the week using the below query.

SET DATEFIRST 1;

Finally, execute the below query to check the weekday number.

SELECT DATEPART(weekday, '2025-07-10') AS WeekdayNumber;

After executing the above query, I got the expected output as shown in the screenshot below.

Here’s how different @@DATEFIRST values map to different week start days:

Approach 4: Using Day of Week Functions with CONVERT Tricks

For some specialized needs, you might want to get the ISO weekday where Monday is one and Sunday is 7, regardless of the @@DATEFIRST setting. Here’s a reliable method you can use.

SELECT (DATEPART(weekday, '2023-07-04') + @@DATEFIRST - 2) % 7 + 1 AS ISOWeekday;

This formula ensures that Monday is always 1 and Sunday is always 7, adhering to the ISO standard regardless of your server’s configuration.

Creating a Custom Weekday Name Function

Sometimes you need more control over weekday names. Here’s a custom function I created:

CREATE FUNCTION dbo.GetCustomWeekdayName (@Date date)
RETURNS varchar(10)
AS
BEGIN
    DECLARE @Weekday int = DATEPART(weekday, @Date)
    
    RETURN CASE @Weekday
        WHEN 1 THEN 'Sun'
        WHEN 2 THEN 'Mon'
        WHEN 3 THEN 'Tue'
        WHEN 4 THEN 'Wed'
        WHEN 5 THEN 'Thu'
        WHEN 6 THEN 'Fri'
        WHEN 7 THEN 'Sat'
    END
END;

After executing the above query, the function was created successfully as shown in the screenshot below.

Best Practices

Below are the best practices for working with weekday extraction:

1. Index date columns: Ensure your date columns are properly indexed to optimize weekday extraction queries.
2. Avoid FORMAT() for large datasets: While convenient, the FORMAT() Function can be significantly slower than DATENAME() or DATEPART().
3. Use computed columns for frequent operations: If you frequently query by weekday, consider adding a computed column:

Various methods can be used to work with dates in SQL Server, but choosing the right approach based on performance needs is crucial.

Conclusion

Extracting weekday information from dates in SQL Server is crucial. Whether you’re using the simple DATENAME() or any other, understanding these techniques will enhance the performance.

Remember that the choice of method depends on your specific requirements:

• Use DATENAME() for human-readable weekday names
• Use DATEPART() for numeric weekday values
• Consider @@DATEFIRST Settings for international applications
• Implement custom functions for specialized business requirements

By applying the techniques explained in this article, you can easily handle any weekday extraction requirement in SQL Server.

You may also like the following articles.

• SQL Server Get The Latest Record By Date
• Convert dd/mm/yyyy to date SQL Server
• SQL Server Check If Date Is Today
• SQL Server Short Date
• SQL Server First Date Of Month

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.