Get MonthWise Calender in sql server

hi,
                              In this article we will see,how to get month wise calender in tabular format.
in rare cases we want do this.

1) Create below procedure::

Create proc get_month

@month int
as
begin
declare @newdate datetime
declare @year varchar(50)
select @year=DateName(year,getdate());;
if len(@month)=1
begin
SELECT @newdate = convert(DATETIME,Convert(varchar, Convert(varchar,@year)+'0'+ Convert(varchar,@month)+'15'), 115)
end
else
begin

SELECT @newdate = convert(DATETIME,Convert(varchar, Convert(varchar,@year)+Convert(varchar,@month)+'15'), 115)

end

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SELECT @endDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@newdate))),DATEADD(mm,1,@newdate)),101)
print @endDate
SELECT @startDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@newdate)-1),@newdate),101);
print @startDate;

WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,[Date])
FROM dates
WHERE DATE < @enddate
)
SELECT Date into #temp
FROM dates
OPTION (MAXRECURSION 0)

--select * from #temp

declare @end int
select @end=DateName(day,@endDate)

create table #data (Monday varchar(50),
Tuesday varchar(50),
Wednesday varchar(50),
Thursday varchar(50),
Friday varchar(50),
Saturday varchar(50),
Sunday varchar(50))
declare @date varchar(100)
declare @datename varchar(100)
declare @flag varchar(100)
declare @str varchar(max)
set @flag ='sandip'

set @str=''
declare @getdateName CURSOR
set @getdateName = CURSOR for
select date from #temp
open @getdateName
fetch next
from @getdateName into @date
while @@FETCH_STATUS = 0
begin
if @flag='sandip'
begin
print 'sandip'
select @datename= DateName(weekday,@date)
select @date=DateName(day,@date)

PRINT @datename
PRINT @date
if(@datename='Monday')
begin
set @str =@date
end


if(@datename='Tuesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Wednesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+@date

END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Thursday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Friday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end

if(@datename='Saturday')
begin

IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Sunday')
begin

if @str=''
begin
print 'end'
set @str ='0'+','+'0'+','+'0'+','+'0'+','+'0'+','+'0'+','+@date
end
else
begin
set @str =@str+','+@date
end

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
set @str=''
set @flag='0'
end
end
else
begin
select @datename= DateName(weekday,@date)
select @date=DateName(day,@date)
if(@datename='Monday')
begin
set @str =@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end


end
if(@datename='Tuesday')
begin
set @str =@str+','+@date
if @end =@date
begin
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end
end
if(@datename='Wednesday')
begin
set @str =@str+','+@date
if @end =@date
begin
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end

if(@datename='Thursday')
begin
set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end

if(@datename='Friday')
begin
set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end
if(@datename='Saturday')
begin


set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end
end
if(@datename='Sunday')
begin
set @str =@str+','+@date
print @str
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
set @str=''
end
end
fetch next
from @getdateName into @date
end
close @getdateName
deallocate @getdateName
select * from #data
end

test case::

exec get_month 12