Get Day of Week in SQL Server

Understanding DATEPART

The DATEPART function is a powerful tool in SQL Server for extracting specific parts of a date or time value. It takes two arguments:

1. Datepart: Specifies the part of the date or time you want to extract. In this case, we'll use WEEKDAY to get the day of the week.
2. Date: The date or time value from which you want to extract the day of the week.

Syntax:

DATEPART(WEEKDAY, date)

Example:

To get the day of the week for the current date:

SELECT DATEPART(WEEKDAY, GETDATE()) AS DayOfWeek;

This will return an integer value representing the day of the week, where 1 is Sunday, 2 is Monday, and so on.

Additional Notes:

• You can use a specific date instead of GETDATE() to get the day of the week for that date.
• If you prefer to get the day of the week as a string (e.g., "Monday"), you can use the DATENAME function:

SELECT DATENAME(WEEKDAY, GETDATE()) AS DayOfWeek;

• The WEEKDAY datepart returns a value based on the server's regional settings. If you need a consistent day of the week format, you might consider using a custom function or stored procedure.

Example with a Specific Date:

SELECT DATEPART(WEEKDAY, '2023-12-25') AS ChristmasDayOfWeek;

This will return 2, indicating that Christmas Day in 2023 was a Monday.

Understanding the Code Examples

Getting the Day of the Week in SQL Server 2005/2008

The primary method to retrieve the day of the week in SQL Server 2005/2008 is using the DATEPART function. This function extracts specific parts of a date or time value.

SELECT DATEPART(WEEKDAY, GETDATE()) AS DayOfWeek;

• DATEPART(WEEKDAY, GETDATE()):
  • DATEPART: Extracts a specific part of a date or time value.
  • WEEKDAY: Specifies that you want to extract the day of the week.
  • GETDATE(): Returns the current date and time.

The DATEPART function remains the core method for getting the day of the week in later versions of SQL Server as well. However, there are additional options available:

Using DATENAME:

SELECT DATENAME(WEEKDAY, GETDATE()) AS DayOfWeek;

• DATENAME: Returns a string representation of a date or time part.

This query will return the day of the week as a text string (e.g., "Monday", "Tuesday").

Using FORMAT (SQL Server 2012 and later):

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

• FORMAT: Provides more flexibility for formatting dates and times.
• 'dddd': Specifies a custom format for the day of the week.

Key Points:

• The DATEPART function is essential for extracting specific date or time parts.
• WEEKDAY is used to get the day of the week.
• DATENAME provides a string representation.
• FORMAT offers more customization options for formatting dates.

Alternative Methods for Getting the Day of the Week in SQL Server

While the DATEPART function is the primary method, there are a few alternative approaches you can consider:

Using a Case Statement:

SELECT CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 1 THEN 'Sunday'
            WHEN DATEPART(WEEKDAY, GETDATE()) = 2 THEN 'Monday'
            -- ... continue for other days of the week
            ELSE 'Unknown' END AS DayOfWeek;

This method involves using a CASE statement to explicitly map the integer returned by DATEPART(WEEKDAY, GETDATE()) to the corresponding day of the week.

Creating a User-Defined Function:

CREATE FUNCTION dbo.GetDayOfWeekName (@date DATE)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @dayOfWeek INT = DATEPART(WEEKDAY, @date);
    DECLARE @dayOfWeekName VARCHAR(10);

    SELECT @dayOfWeekName = CASE 
        WHEN @dayOfWeek = 1 THEN 'Sunday'
        WHEN @dayOfWeek = 2 THEN 'Monday'
        -- ... continue for other days of the week
        ELSE 'Unknown' END;

    RETURN @dayOfWeekName;
END

This approach defines a user-defined function that takes a date as input and returns the corresponding day of the week as a string. You can then use the function in your queries:

SELECT dbo.GetDayOfWeekName(GETDATE()) AS DayOfWeek;

Using a Lookup Table:

Create a table with two columns: one for the day of the week number and another for the corresponding day of the week name. Then, you can join your query with this table to get the day of the week.

Table:

CREATE TABLE dbo.DayOfWeekLookup (
    DayOfWeekNumber INT PRIMARY KEY,
    DayOfWeekName VARCHAR(10)
);

INSERT INTO dbo.DayOfWeekLookup VALUES
    (1, 'Sunday'),
    (2, 'Monday'),
    -- ... continue for other days of the week

Query:

SELECT d.DayOfWeekName
FROM YourTable t
JOIN dbo.DayOfWeekLookup d ON t.DayOfWeekNumber = d.DayOfWeekNumber;