SQL Calendar

Make a function
—————————

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

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.

exec calender 06,2013

source http://sqlservergeeks.com/blogs/metal/personal/845/sql-server-fun-with-common-table-expression-creating-a-calendar