Get day of week in SQL Server 2005/2008

How to Get the Day of the Week in SQL Server 2005/2008

Learn various methods to extract the day of the week from a date in SQL Server 2005 and 2008, including using DATEPART, DATENAME, and custom formatting.

Extracting the day of the week from a DATETIME column is a common requirement in SQL Server for reporting, data analysis, or conditional logic. While newer versions of SQL Server offer more advanced functions, SQL Server 2005 and 2008 provide robust ways to achieve this using built-in functions like DATEPART and DATENAME. This article will guide you through the most effective methods, including considerations for language and regional settings.

Using DATEPART for Numeric Day of Week

The DATEPART function is ideal when you need the day of the week as an integer. It returns an integer representing the specified datepart of the specified date. For the day of the week, you'll use the weekday or dw datepart. By default, SQL Server considers Sunday as 1, Monday as 2, and so on, up to Saturday as 7. However, this can be influenced by the @@DATEFIRST setting.

SELECT DATEPART(weekday, GETDATE()) AS DayOfWeekNumber;
SELECT DATEPART(dw, '2023-10-26') AS SpecificDayOfWeekNumber;

Examples of using DATEPART to get the numeric day of the week.

Understanding @@DATEFIRST and Its Impact

The @@DATEFIRST setting is a global configuration that defines which day is considered the first day of the week. This directly impacts the integer returned by DATEPART(weekday, date). For example, if @@DATEFIRST is set to 1 (Monday), then Monday will return 1, Tuesday 2, and so on. If it's set to 7 (Sunday), then Sunday will return 1. You can check the current setting and change it for your session.

SELECT @@DATEFIRST AS CurrentDateFirstSetting;

-- Example: Set Monday as the first day of the week for the current session
SET DATEFIRST 1;
SELECT DATEPART(weekday, '2023-10-26') AS DayOfWeekNumber_MondayFirst; -- Thursday

-- Example: Set Sunday as the first day of the week for the current session
SET DATEFIRST 7;
SELECT DATEPART(weekday, '2023-10-26') AS DayOfWeekNumber_SundayFirst; -- Thursday

Demonstrating the effect of @@DATEFIRST on DATEPART(weekday).

flowchart TD
    A[Input Date] --> B{DATEPART(weekday, Date)}
    B --> C{Check @@DATEFIRST Setting}
    C -->|@@DATEFIRST = 1 (Monday)| D[Output: 1=Mon, 2=Tue...]
    C -->|@@DATEFIRST = 7 (Sunday)| E[Output: 1=Sun, 2=Mon...]
    C -->|Other @@DATEFIRST Value| F[Output: Varies based on setting]
    D --> G[Numeric Day of Week]
    E --> G
    F --> G

Flowchart illustrating how @@DATEFIRST influences DATEPART(weekday) output.

Using DATENAME for Full Day Name

When you need the full name of the day (e.g., 'Monday', 'Tuesday'), the DATENAME function is the appropriate choice. Similar to DATEPART, it also accepts weekday or dw as the datepart. The output of DATENAME is a NVARCHAR string and is influenced by the server's language setting.

SELECT DATENAME(weekday, GETDATE()) AS DayOfWeekName;
SELECT DATENAME(dw, '2023-10-26') AS SpecificDayOfWeekName;

Examples of using DATENAME to get the full day of the week name.

Customizing Day of Week Output

Sometimes, you might need a specific format for the day of the week, such as an abbreviated name ('Mon', 'Tue') or a custom mapping. While SQL Server 2005/2008 doesn't have a direct FORMAT function like newer versions, you can achieve this using a CASE statement or by combining DATEPART with an array/table lookup.

-- Using CASE statement for custom abbreviation (assuming @@DATEFIRST is 7 for Sunday=1)
SELECT
    CASE DATEPART(weekday, '2023-10-26')
        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 AS AbbreviatedDayName;

-- Using a temporary table for lookup (more flexible for different languages/formats)
DECLARE @DayNames TABLE (DayNumber INT, ShortName NVARCHAR(3), FullName NVARCHAR(10));
INSERT INTO @DayNames VALUES
(1, 'Sun', 'Sunday'), (2, 'Mon', 'Monday'), (3, 'Tue', 'Tuesday'),
(4, 'Wed', 'Wednesday'), (5, 'Thu', 'Thursday'), (6, 'Fri', 'Friday'), (7, 'Sat', 'Saturday');

SELECT dn.ShortName
FROM @DayNames dn
WHERE dn.DayNumber = DATEPART(weekday, '2023-10-26');

Customizing day of week output using CASE and a lookup table.