SQL SERVER - UDF - Get the Day of the Week Function

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.

Create  FUNCTION [dbo].[DayOfWeek](@dtDate DATETIME)

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @rtDayofWeek VARCHAR(10)

DECLARE @weekDay int

--Here I have subtracted 7 For keeping Sunday as the First day

-- like wise for Monday we need to subtract 2 and so on

set @weekDay = (Select ((DatePart(dw,@dtDate)+@@DATEFIRST-7)%7)) 

SELECT @rtDayofWeek = CASE @weekDay

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

END