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

Written by

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())

SQL Server Transformation

Comments

7 responses to “How to get day of the week from a datetime in SQL Server”

October 12, 2011

Ulf

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
April 10, 2013

ps3 ylod fix

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

Reply
February 18, 2014

free

🙂 🙂 Very Useful

Reply
September 4, 2014

fadray

Reply
May 31, 2016

Ákos

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
June 22, 2016

Datetime In Sql Server | mesotheliomacare.xyz

[…] How to get day of the week from a datetime in SQL Server … – 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). […]

Reply
July 21, 2016

JustAnotherDBA

What if @@DATEFIRST is NOT 1 ?

Reply