How to Find Day Name From Date in SQL Server?

Finding the day name from a specific date is a common task in SQL Server, useful for generating reports, analyzing trends, or scheduling. SQL Server provides two primary methods such as the DATENAME() function and the FORMAT() function.

In this article, We will learn about How to Find Day Name From the Date in an SQL Server in detail and so on.

How to Find Day Name From Date in an SQL Server?

To find the Day Name From Date in SQL Server we will use the below method for better understanding as defined below:

1. Using the DATENAME() Function
   
2. Using FORMAT() Function

1. Using the DATENAME() Function

The DATENAME() function in SQL Server finds a given part of the specified date. Moreover, it returns the output value as a string.

Syntax

Syntax finds the day name from a date using the DATENAME() function:

DATENAME(type, Date)

Example 1:

In this example, we will find the day name from a given date using DATETIME function in SQL Server.

Suppose we want to declare a specific date and retrieve both the date and the day of the week name for that date.

DECLARE @Date DATE = '2020-12-22';
SELECT @Date As [TDate],  
DATENAME(WEEKDAY, @Date) AS [Day_Name];

Output:

Explanation: This query declares a variable @Date of type DATE with the value '2020-12-22'. It then selects two columns: the first column returns the value of @Date as TDate, and the second column uses the DATENAME(WEEKDAY, @Date) function to return the name of the day of the week (e.g., Tuesday) for the given date. The WEEKDAY argument in DATENAME specifies that the function should return the full name of the day

Example 2:

Query:

Suppose we want to declare a date and retrieve both the date itself and the name of the day (e.g., Monday, Tuesday) corresponding to that date.

DECLARE @Date DATE = '2022-12-22';
SELECT @Date AS [TDate],  
DATENAME(w, @Date) AS [Day_Name];

Output: 

Explanation: This query declares a variable @Date of type DATE with the value '2022-12-22'. It then selects two columns: the first returns the value of @Date as TDate, and the second column returns the name of the day (e.g., Thursday) using the DATENAME(w, @Date) function, where w represents the day of the week

2. Using FORMAT() Function

The FORMAT() function is one of the String functions in SQL Server, which is used to format the specified value in the given format. We can use this function to extract and format the date in day name format.

Syntax:

Syntax to find day name from a date using FORMAT function is:

FORMAT(Date, 'dddd')

Example 1:

In this example, we will find the day name from a given date using FORMAT function in SQL Server.

DECLARE @Date DATE = '2021-12-24';
SELECT @Date As [TDate],  
FORMAT(@Date, 'dddd') AS [Day_Name]

Output:

Example 2:

Let us suppose we have below table name "GeekLogin":

Step 1: To Create Table

Query:

CREATE TABLE GeekLogin 
( Name varchar (22),
 ID int, LoginDate date) ;

Step 2: Insert Values in the table.

Query:

INSERT INTO GeekLogin (Name, LoginId, LoginDate)
VALUES
    ('Khushi', 2, '2019-07-22'),
    ('Megha', 4, '2019-09-23'),
    ('Komal', 3, '2019-08-27'),
    ('Mona', 5, '2019-12-19'),
    ('Ankit', 7, '2019-09-12'),
    ('Deepak', 8, '2019-09-04');

Step 3: To check LoginDay for LoginDate, we could use the below query.

Query:

Suppose we need to retrieve the names, IDs, and login dates of users from the GeekLogin table, along with the day of the week on which each login occurred.

SELECT TOP (1000) [Name],[ID],[LoginDate], 
DATENAME(w, LoginDate) AS [LoginDay]
FROM [GeekLogin];

Output:

Conclusion

In SQL Server, the DATENAME() and FORMAT() functions are powerful tools for extracting the day name from a date. The DATENAME() function offers a straightforward approach for retrieving the full name of the day of the week, while the FORMAT() function provides flexibility in formatting dates into various string representations.