Get Day Name From Date SQL Server

In this article, I will guide you in retrieving day names from dates in SQL Server, covering every proven method, performance optimization technique, and real-world application.

Get Day Name From Date SQL Server

Before exploring specific SQL Server functions and techniques, let me establish why day name extraction represents such a critical capability.

• Retail Analytics: Analyzing customer shopping patterns
• Healthcare Operations: Managing staff schedules and patient appointments in medical facilities
• Financial Services: Creating weekly performance reports for banks and investment firms
• Supply Chain Management: Optimizing delivery schedules and warehouse operations based on weekday demand patterns
• E-commerce Intelligence: Understanding online consumer behavior trends throughout the week

SQL Server Date Architecture Overview

Primary Date Data Types :

Method 1: Using the DATENAME Function

The DATENAME function consistently proves to be the most reliable, performant, and widely-supported method for extracting day names from dates.

Syntax

DATENAME(datepart, date_expression)

Essential Parameters:

• datepart: Specifies the date component to extract (weekday, dw, or w for day names)
• date_expression: The date value, column, or expression from which to extract the day name
• Return Type: NVARCHAR string containing the localized day name

Supported Datepart Values for Day Extraction:

Practical DATENAME Implementation Strategies

Basic Day Name Extraction Queries:

Single Date Processing:

Example 1

We can execute the query below to extract day name from current system date.

SELECT DATENAME(weekday, GETDATE()) AS CurrentDayName;

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

Example 2

We can execute the query below to extract the day name from a specific date literal.

SELECT DATENAME(dw, '2025-09-14') AS SpecificDayName;

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

Example 3

We can execute the query below to extract the day name with date arithmetic.

SELECT DATENAME(weekday, DATEADD(day, 7, GETDATE())) AS NextWeekDayName;

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

Table-Based Data Processing:

Example 4

We can execute the query below to extract the day names from sales data.

-- Extract day names from sales data
SELECT 
    sale_amount,
    TotalSales,
    DATENAME(weekday, sale_date) AS TransactionDay,
    sale_amount
FROM Sales
WHERE sale_date >= DATEADD(month, -6, GETDATE())
ORDER BY sale_date DESC;

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

Advanced Analytical Queries:

Example 6

We can execute the query below to extract the weekly performance analysis by day name.

-- Weekly performance analysis by day name
SELECT 
    DATENAME(weekday, SaleDate) AS WeekDay,
    COUNT(*) AS TotalTransactions,
    SUM(SaleAmount) AS TotalRevenue,
    AVG(SaleAmount) AS AverageTransactionValue,
    MAX(SaleAmount) AS HighestTransaction
FROM SalesData
WHERE SaleDate BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
GROUP BY DATENAME(weekday, SaleDate)
ORDER BY 
    CASE DATENAME(weekday, SaleDate)
        WHEN 'Monday' THEN 1
        WHEN 'Tuesday' THEN 2
        WHEN 'Wednesday' THEN 3
        WHEN 'Thursday' THEN 4
        WHEN 'Friday' THEN 5
        WHEN 'Saturday' THEN 6
        WHEN 'Sunday' THEN 7
    END;

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

DATENAME Performance Characteristics and Optimization

Performance Advantages:

• Native Function: Built into SQL Server engine for optimal execution speed
• Index Compatibility: Supports computed columns and can be included in indexed views
• Memory Efficiency: Minimal memory allocation for string operations
• Query Plan Optimization: Efficient execution plan generation for both single-row and bulk operations

Enterprise Optimization Strategies:

• Computed Column Implementation: Pre-calculate day names for frequently queried tables
• Indexed View Creation: Build materialized views containing day name aggregations
• Plan Cache Utilization: Use SQL Server’s query plan caching for repeated operations
• Columnstore Integration: Optimize for analytical workloads using columnstore indexes

Method 2: Using the FORMAT Function

Introduced in SQL Server 2012, the FORMAT function provides enhanced formatting capabilities.

FORMAT Function Implementation for Day Names

Syntax

FORMAT(date_value, format_string [, culture])

Day Name Format Specifications:

Comprehensive FORMAT Examples:

Basic Day Name Formatting:

Example 1

We can execute the query below to retrieve the full day name extraction using the FORMAT function.

SELECT FORMAT(GETDATE(), 'dddd') AS TodayFullName;

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

Example 2

We can execute the query below, which involves extracting abbreviated day names.

SELECT FORMAT(GETDATE(), 'ddd') AS TodayAbbreviated;

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

Example 3

We can execute the query below for custom combined formatting.

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS FormattedToday;

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

Business Report Integration:

Example 3

We can execute the query below for the advanced sales reporting.

-- Advanced Sales reporting
SELECT 
    TotalSales,
    FORMAT(sale_date, 'dddd') AS TransactionDayFull,
    FORMAT(sale_date, 'ddd') AS TransactionDayShort,
    FORMAT(sale_date, 'dddd, MMMM dd, yyyy') AS FormattedTransactionDate,
    sale_Amount
FROM Sales
WHERE YEAR(sale_date) = YEAR(GETDATE())
ORDER BY sale_date DESC;

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

Cultural Formatting for Diverse Markets:

Example 4

We can execute the query below for the multi-cultural day name formatting.

-- Multi-cultural day name formatting for American businesses
SELECT 
    GETDATE() AS SystemDate,
    FORMAT(GETDATE(), 'dddd', 'en-US') AS EnglishDayName,
    FORMAT(GETDATE(), 'dddd', 'es-US') AS SpanishDayName,
    FORMAT(GETDATE(), 'dddd', 'fr-US') AS FrenchDayName

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

FORMAT Function Considerations

Advantages for American Enterprises:

• Formatting Flexibility: Extensive customization options for business requirements
• Internationalization Support: Multi-language capabilities for diverse American markets
• Modern Approach: Aligns with contemporary .NET formatting standards
• Presentation Ready: Direct output suitable for reports and user interfaces

Performance and Compatibility Considerations:

• Processing Overhead: Higher computational cost compared to DATENAME
• Version Requirements: Requires SQL Server 2012 or later versions
• Memory Usage: Additional memory allocation for complex format string processing
• Execution Speed: Slower performance in high-volume scenarios

Method 3: DATEPART with CASE Statements – Maximum Control Approach

Combining DATEPART with CASE statements provides maximum flexibility and backward compatibility.

DATEPART and CASE Implementation Strategy

Core Implementation Pattern:

Example 1

-- Custom day name extraction using DATEPART and CASE
SELECT 
    sale_date,
    CASE DATEPART(weekday, sale_date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'  
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END AS OrderDayName
FROM Sales;

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

Advanced Business Logic Integration:

Example 6

-- Business-specific day categorization for American operations
SELECT 
    Sale_Date,
    Sale_Amount,
    CASE DATEPART(weekday, Sale_Date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END AS SaleDayName,
    CASE 
        WHEN DATEPART(weekday, Sale_Date) IN (1, 7) THEN 'Weekend'
        ELSE 'Weekday'
    END AS DayCategory,
    CASE 
        WHEN DATEPART(weekday, Sale_Date) BETWEEN 2 AND 6 THEN 'Business Day'
        ELSE 'Non-Business Day'
    END AS BusinessDayStatus
FROM Sales;

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

Video Tutorial

Conclusion

Knowing day name extraction in SQL Server is an essential skill for building user-friendly applications and comprehensive reporting solutions.

Choose the proper function for your SQL Server version. While DATENAME() remains the most reliable and widely compatible option, newer functions like FORMAT() offer enhanced formatting capabilities for modern applications. I always recommend DATENAME() for production systems requiring maximum compatibility and performance.

You may also like the following articles.

• SQL Server Days Between Date and Today
• How to Get Quarter from Date in SQL Server
• SQL Server Select Date Older Than 30 Days

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.