SQL function to get weekday – weekly calendar

In this blog post we’ll look at a way to list out all week days of a week a particular date belongs too. This can be even referred to as weekly calendar. This comes handy in reporting. A SQL function to get week days of particular date is given below

First, create the below function to be used as base for the main query

ALTER FUNCTION [dbo].fn_Calendar
(
   
)
RETURNS TABLE AS RETURN
(
    WITH CTE_Calendar([Date])
AS
(
  SELECT CAST('19990101' as date) 
  UNION ALL
  SELECT   DATEADD(dd, 1, [Date])
  FROM CTE_Calendar
  WHERE DATEADD(dd, 1, [Date]) <= '25001231'
)
SELECT [Date], 
       [Day]=Datename(dw, [Date]), 
       [MonthName]=Datename(month, [Date]), 
       [Year]=Datepart(yy, [Date]) ,
	   [WeekNumber]=Datepart(WK, [Date])
FROM   CTE_Calendar  
)

The above function returns a calendar from 19990101 to 25001231. The output from the above function is shown below

The week days for any date can now be fetched by filtering on week start and end date as shown below (week starts on Sunday)

DECLARE @Date Date;
SET @Date=GETDATE(); 
SELECT 
	* 
	FROM  dbo.fn_Calendar() As Calendar
	WHERE [Date]>=DATEADD(dd, -(DATEPART(dw, @Date)-1), @Date)
	AND  [Date] <=  DATEADD(dd, 7-(DATEPART(dw, @Date)), @Date)
	OPTION (MAXRECURSION 0)

The output from above statement is shown below

The output can be modified in calendar format as shown below

DECLARE @Date Date;
SET @Date=GETDATE(); 
SELECT 
	[Sunday],
	[Monday],
	[Tuesday],
	[Wednesday],
	[Thursday],
	[Friday],
	[Saturday]
FROM 
(
	SELECT [Day]=Datename(dw, [Date]), [Date]
	FROM dbo.fn_Calendar() 
	WHERE [Date]>=DATEADD(dd, -(DATEPART(dw, @Date)-1), @Date)
	AND  [Date] <=  DATEADD(dd, 7-(DATEPART(dw, @Date)), @Date)
	
) As Src 
PIVOT 
(
MAX([Date]) FOR [Day] in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) As pvt OPTION (MAXRECURSION 0)

The above query pivots the data around days of the week. The result from the above query is shown below

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook