Understanding Day-of-Week Calculation in SQL Server

Introduction

In SQL Server, extracting specific components of a date, such as the day of the week, is a common task. Whether for scheduling reports or analyzing time-based data, knowing how to determine the day name from a given date can be crucial. This tutorial will guide you through different methods to achieve this in SQL Server 2005 and 2008.

Built-in Functions: DATENAME and DATEPART

SQL Server provides built-in functions like DATENAME and DATEPART for date manipulation:

• DATENAME(dw, date): This function returns the name of the day (e.g., "Monday") from a specified date.

  SELECT DATENAME(dw, '2009-01-01') AS DayName; -- Returns "Sunday"
  

• DATEPART(dw, date): This function returns an integer representing the day of the week (1 for Sunday through 7 for Saturday by default).

  SELECT DATEPART(dw, '2009-01-01') AS DayNumber; -- Returns 7 (Sunday)
  

Understanding SET DATEFIRST

The behavior of DATEPART can vary based on the @@datefirst setting, which defines the first day of the week. By default, it’s set to Sunday. You can change this using:

SET DATEFIRST 2; -- Sets Tuesday as the first day of the week

Changing DATEFIRST affects how DATEPART interprets days of the week but not DATENAME. For example:

DECLARE @CurrentDayName NVARCHAR(10);
DECLARE @CurrentDayNumber INT;

SET DATEFIRST 2; -- Tuesday is now the first day

SELECT @CurrentDayName = DATENAME(dw, GETDATE());
SELECT @CurrentDayNumber = DATEPART(dw, GETDATE());

PRINT @CurrentDayName; -- Still returns "Saturday"
PRINT @CurrentDayNumber; -- Returns 5 (Saturday, as it's five days after Tuesday)

Mapping Day Numbers to Names

If you need the name of the day and want control over @@datefirst, use a combination of DATEPART with a case statement:

SELECT 
    CASE DATEPART(dw, '2009-01-01')
        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 DayName;

Deterministic Day-of-Week Calculation

To ensure a consistent day-of-week calculation regardless of @@datefirst, use a formula:

DECLARE @d DATETIME = '2009-01-01';
DECLARE @dayOfWeek INT;

SET @dayOfWeek = ((DATEPART(dw, @d) + @@DATEFIRST - 2) % 7) + 1;

SELECT 
    CASE @dayOfWeek
        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 DayName;

Conclusion

Understanding how to calculate the day of the week in SQL Server is essential for time-sensitive operations. By using DATENAME, DATEPART, and understanding server settings like @@datefirst, you can accurately determine the day name from any date. This knowledge ensures that your data analysis remains consistent across different environments.