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

1_SQ function to get weekday - weekly calendar

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

2_SQ function to get weekday - weekly calendar

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

3_SQ function to get weekday - weekly calendar

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *