How to Get Week Day Name using T-SQL in MS SQL Server
Database developers use SQL datetime function DATENAME to get day name in SQL Server database like Monday, Tuesday, etc.
Before DateName SQL function is introduced, in T-SQL there was not a built-in datetime function which gets and returns the week day name of a given date.
For example, you need to day name like Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday. Now SQL programmers are able to use SQL DATENAME function.
Here is the sample SQL Select statement code where SQL DATENAME build-in datetime function is used to the get day name of a given date in SQL Server.
SELECT
DATENAME(dw, GETDATE()),
DATENAME(dw, DATEADD(dd, 1, GETDATE())),
DATENAME(dw, DATEADD(dd, 2, GETDATE())),
DATENAME(dw, DATEADD(dd, 3, GETDATE())),
DATENAME(dw, DATEADD(dd, 4, GETDATE())),
DATENAME(dw, DATEADD(dd, 5, GETDATE())),
DATENAME(dw, DATEADD(dd, 6, GETDATE()))
It is also possible to set database language property to an other language for selecting day names or month names in that target language.
As an alternative method, previously I used following SQL code to find the day name in local language.
The main built-in datetime function which will help us to get the day as its week day name is DATEPART function with dw (weekday) argument.
The return type of the DATEPART function is int. So using DATEPART with dw (weekday) argument will return an integer value between 1 and 7.
Also the return value of DATEPART with weekday or dw argument is dependent on @@DATEFIRST which specifies the first day of the week.
The @@DATEFIRST value for US English is 7 by default which points to Sunday.
Here, you can find below a T-SQL user-defined function which returns the week day of a date send as parameter to the user defined function taking into consideration of @@DateFirst server parameter.
Since I used to work on SQL Servers distributed on different parts of the World, the @@DateFirst parameter could be set different among database servers.
So following SQL function was preventing these problems when DATEPART() SQL function returns different values on different database servers.
----------------------------------------
--- GetWeekDayNameOfDate
--- Returns Week Day Name in English
--- Takes @@DATEFIRST into consideration
--- You can edit udf for other languages
----------------------------------------
CREATE FUNCTION GetWeekDayNameOfDate
(
@Date datetime
)
RETURNS nvarchar(50)
BEGIN
DECLARE @DayName nvarchar(50)
SELECT
@DayName =
CASE (DATEPART(dw, @Date) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
RETURN @DayName
END
GO
And SQL developers and database administrator can call the GetWeekDayNameOfDate user-defined SQL function in a SELECT sql statement as shown in the below t-sql sample script:
SELECT dbo.GetWeekDayNameOfDate(GETDATE()) as WeekDayName