SQL Server Common Table Expression – Creating a Calendar

Friends,

In this post I am going to discuss, how to retrieve a month’s calendar by passing month and year as parameters to the script or procedure. The important point to note here is that if we use SQL Server Common Table Expression there is no need to create temporary table or table variable to store and then iterate over the days and dates of the month.

Before we proceed to our proc, copy the two functions below and run them in SSMS.

USE [AdventureWorks]
GO
------ Function to return the First Date for month
CREATE function [dbo].[f_FirstDayOfMonth]
(
 @date date
)
returns date
as
 
begin
declare @answer date, @month varchar(2), @year char(4)

set @month = case when datepart(month,@date) < 10 then '0' else '' end + convert(varchar(2), datepart(month,@date))
 
set @year = convert(char(4),datepart(year,@date))
set @Answer = convert(date,@month + '/01/' + @year)
 
return @answer
end
Go
 
------ Function to return the Last Date for month
CREATE function [dbo].[f_LastdayOfMonth]
(
 @date date
)
returns date
as
 
begin
declare @answer date, @month varchar(2), @year char(4)
 
set @month = case when datepart(month,@date) < 10 then '0' else '' end + convert(varchar(2),datepart(month,@date))
 
set @year = convert(char(4),datepart(year,@date))
 
set @Answer = convert(date,@month + '/01/' + @year)
set @Answer = dateadd(month,1,@Answer)
set @Answer = dateadd(day,-1,@answer)
 
return @answer
end
GO

Copy and paste the below proc and run in SSMS.

USE [AdventureWorks]
GO
 
CREATE Procedure [dbo].[Calender]
(
 @month tinyint,
 @year int
)
as
Begin
 
---------- Declare Valiables
 
Declare @date1 date, @enddate date, @day1 varchar(10), @weekid tinyint, @currdate date
 
Select @currdate= convert(date,(CAST(@year as char(4))+'-'+CAST(@month as varchar(2))+'-15'))
 
Select @date1=convert(date,dbo.[f_FirstDayOfMonth](@currdate)), @enddate=convert(date,dbo.[f_LastdayOfMonth](@currdate))
 
Select @day1= DATENAME(WEEKDAY, @date1)
 
---------- Recursive CTE to get Days and Dates for the month
 
;with cte_cal ([Date], [Day], [weekid])
as
(
 Select @date1, @day1, case when DATEPART(WEEKDAY,@date1)=1 then cast(DATEPART(WW,@date1)  as tinyint)-1
                            else DATEPART(WW,@date1) end as weekid
 union all
 Select DATEADD(DD,1,[Date]), cast(DATENAME(WEEKDAY, DATEADD(DD,1,[Date])) as varchar(10)),  
                              case when DATEPART(WEEKDAY,DATEADD(DD,1,[Date]))=1 then
                              cast(DATEPART(WW,DATEADD(DD,1,[Date]))  as tinyint)-1
	                              else DATEPART(WW,DATEADD(DD,1,[Date])) end as weekid
 from cte_cal
 where [Date] < @enddate 
)
 
------- Use Pivot to display the result in calender format
 
Select  [weekid], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday] 
from
(
 Select [Weekid], [Date], [DAY] from cte_cal
)
pvt
Pivot
(
 max([Date]) for [Day] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday] )
)
Pvttab
 
End
GO

Finally, execute the proc with required parameters.

Regards

Mridul Chandhok

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook