How to Get Week Day Name using T-SQL in MS SQL Server
In T-SQL there is not a built-in datetime function which gets and returns the week day name of a send date as parameter.
For example, you need to day name like Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday.
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.
----------------------------------------
--- 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 you can call the GetWeekDayNameOfDate user-defined function in a SELECT sql statement as shown in the below t-sql sample script :
SELECT dbo.GetWeekDayNameOfDate(GETDATE()) as WeekDayName
|