SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems