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

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()))
use SQL DATENAME to get day name of given date in SQL Server

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



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 - 2015 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems