How to get day of the week from a datetime in SQL Server

Updated on December 3, 2009 By spyriadis 7 Comments

The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.

Method 1:
Create function running following script:

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
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 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO

Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
ResultSet:
DayOfWeek
———-
Monday

Method 2:

SELECT DATENAME(dw, GETDATE())

By spyriadis

7 Comments

Ulf October 12, 2011

To make you function independent of the current system settings on the SQL server you should rather use this method:
MS SQL: Day of Week

Reply
ps3 ylod fix April 10, 2013

Hi there, everything is going sound here and ofcourse every one is sharing data, that’s in fact good, keep up writing.

Reply
free February 18, 2014

🙂 🙂 Very Useful

Reply
fadray September 4, 2014

Reply
Ákos May 31, 2016

If you want to get the day of the week in different languages use the format function

SELECT
FORMAT(getdate(), ‘dddd’, ‘hu-HU’) AS HUN,
FORMAT(getdate(), ‘dddd’, ‘de-DE’) AS GER,
FORMAT(getdate(), ‘dddd’, ‘en-GB’) AS ENG

Reply
Pingback: Datetime In Sql Server | mesotheliomacare.xyz
JustAnotherDBA July 21, 2016

What if @@DATEFIRST is NOT 1 ?

Reply

7 Comments

Leave a Reply Cancel reply