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