Day of Week Functions

It is often necessary to return the day of the week name, rather than the numeric date.  This is a handy little function for returning the day name, for a given date value:


IF OBJECT_ID('udf_DayOfWeek','FN')>0
DROP FUNCTION dbo.udf_DayOfWeek
GO
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


--Then you can reference the function like this:
  SELECT dbo.udf_DayOfWeek(GETDATE()) [Day Of Week]
  SELECT dbo.udf_DayOfWeek('07/02/2011') [My Next Birthday]


Here's another one that is useful for getting the first day of the week.  This can be very helpful for reporting transactions from the beginning of the week, as opposed to the start of the current day:

IF OBJECT_ID('udf_FirstDayOfWeek','FN')>0

DROP FUNCTION dbo.udf_FirstDayOfWeek

GO

CREATE FUNCTION [dbo].[udf_FirstDayOfWeek] ( 

 @InputDate    DATETIME 

)

RETURNS DATETIME

BEGIN

  SET @InputDate = CONVERT(VARCHAR(10), @InputDate, 111)

  RETURN DATEADD(DD, 1 - DATEPART(DW, @InputDate),@InputDate)

END

GO

--Then you can reference the function like this:

 SELECT dbo.udf_FirstDayOfWeek(GETDATE()) [This Week]

 SELECT dbo.udf_FirstDayOfWeek('07/02/2011') [My Birthday Week]