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:
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: