How to Find Day Name From Date in SQL Server?

There are multiple ways to find a day name from a date in SQL Server. Below we will a few methods to find the day name from any date from the past to the future:

• DATENAME() Function
• FORMAT() Function

Method 1: Use DATENAME() Function 

This function in SQL Server is used to find a given part of the specified date. Moreover, it returns the output value as a string.

Syntax:

DATENAME(type, Date) 

where:
type to find day name could be weekday, dw, w

Query 1:

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

Output:

Instead of weekday, we could also use the abbreviation for it:

Query 2:

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

Output:

Query  3:

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

Output:

Method 2: FORMAT() Function

The FORMAT() function is one of the String functions, which is used to format the specified value in the given format.

Syntax:

FORMAT(Date, 'dddd') 

Query 1: 

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

Output:

Query 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 VALUES ('Khushi',2 ,'2019-07-22');
INSERT INTO GeekLogin VALUES ('Megha',4 ,'2019-09-23');
INSERT INTO GeekLogin VALUES ('Komal',3 ,'2019-08-27');
INSERT INTO GeekLogin VALUES ('Mona',5 ,'2019-12-19');
INSERT INTO GeekLogin VALUES ('Ankit', 7,'2019-09-12');
INSERT INTO GeekLogin VALUES ('Deepak', 8 ,'2019-09-04');

Name	ID	LoginDate
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:

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

Output: