How to create calendar in sql server.?

 first way to create calendar

DECLARE @Month AS INT = 8 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2015 --Set the YEAR for which you want to generate the Calendar.

--Find and set the Start & End Date of the said Month-Year
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

;WITH Dates AS (
  SELECT
    @StartDate Dt
  UNION ALL
  SELECT
    DATEADD(DAY,1,Dt)
  FROM
    Dates
  WHERE
    DATEADD(DAY,1,Dt) <= @EndDate
),Details AS (
  SELECT
    DAY(Dt) CDay,
    DATEPART(WK,Dt) CWeek,
    MONTH(Dt) CMonth,
    YEAR(Dt) CYear,
    DATENAME(WEEKDAY,Dt) DOW,
    Dt
  FROM
    Dates
)
--Selecting the Final Calendar
SELECT
  Sunday,
  Monday,
  Tuesday,
  Wednesday,
  Thursday,
  Friday,
  Saturday
FROM
  (SELECT CWeek,DOW,CDay FROM Details) D
PIVOT
(
  MIN(CDay)
  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
) AS PVT
ORDER BY
  CWeek

 Second way to create calendar

DECLARE @Month AS INT = 8 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2015 --Set the YEAR for which you want to generate the Calendar.
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

SELECT
SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
FROM master.dbo.spt_values v
WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate
AND DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
AND v.type = 'P'
GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate))

  Third way to create calendar

 --CREATE PROCEDURE dbo.spGetMonthCalendar
    DECLARE @Month INT = 8
    DECLARE @Year INT = 2015
--AS BEGIN

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE

    -- Create the start date value
    SET @StartDate = CONVERT(DATE, RIGHT('0000' + CONVERT(VARCHAR, @Year),4) + '-' + RIGHT('00' + CONVERT(VARCHAR, @Month), 2) + '-01')
    -- Create the end date
    SET @EndDate = DATEADD(M,1,@StartDate);

    WITH Sales_CTE (MonthDate)
    AS
    (
    Select
        @StartDate
    UNION ALL
    Select DATEADD(dd, 1, MonthDate) as MonthDate
        from Sales_CTE
    WHERE
        DATEADD(dd, 1, MonthDate) < @EndDate
    )
    select
        [1] as sun,
        [2] as mon,
        [3] as tue,
        [4] as wed,
        [5] as thu,
        [6] as fri,
        [7] as sat
    FROM
        (
        select
            day(MonthDate) as MONTHDATE,
            datepart(weekday, MonthDate) AS DAYOFWEEK,
            DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, MonthDate), 0), MonthDate) +1 as ROW
        from Sales_CTE
        ) as source_table
    pivot (
        sum(monthdate)
        for dayofweek in ([1],[2],[3],[4],[5],[6],[7])
        ) as pivottable
--END

All the above code after executing produce the same output like below.
 

 








 Now how to fill previous month date in calendar and remove null

declare @M int=8, @Y int=2015, @SDate date, @Edate date, @var varchar(50), @var1 varchar(50), @Q varchar(2500), @count int, @r int=0, @firstDay varchar(25)= 'Wed',@TempsDate date

set @SDate = Cast(cast(@Y as varchar) +'-'+cast(@M as varchar)+'-'+'01' as date)
set @TempsDate=@SDate
set @Edate= DATEADD(DD,-1, DATEADD(MM,1,@SDate))

create table #tblweek  ( sl int identity(1,1)  ,Sun varchar(10) default (0),Mon varchar(10) default (0),
Tue varchar(10) default (0),Wed varchar(10) default (0),Thu varchar(10) default (0),Fri varchar(10) default (0),
Sat varchar(10) default (0))
declare @tbl table(cnt int)


while(@SDate<=@Edate)
begin

set @var = datename(dw,@SDate)
set @var =LEFT(@var,3)
set @var1=day(@SDate)
set @Q = 'select count(*) from #tblweek where ['+@var+'] =0 '
delete @tbl
insert into @tbl
exec(@Q)

set @count = (select top 1 * from @tbl)

if(@count> 0)
begin
set @Q = 'select count(*) from #tblweek where [Sat] !=0 and sl = '''+cast(@r as varchar)+''''
delete @tbl
insert into @tbl
exec(@Q)
set @count = (select top 1 * from @tbl)

if(@count>0)
begin

set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
exec(@Q)
set @r = @r +1
end

else
begin
set @Q='update #tblweek set ['+@var+'] = '+@var1+' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
if(@var='Sat')
set @r = @r
end
end
else
begin
        set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
        exec(@Q)
        set @r = @r +1


        -------------------------- For Previous Month-----------------------
        if(@var='Sat')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Fri=day(@TempsDate),Thu=day(@TempsDate)-1,Wed=day(@TempsDate)-2, Tue=day(@TempsDate)-3, Mon=day(@TempsDate)-4,Sun=day(@TempsDate)-5 where sl=1
        end
        else if(@var='Fri')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Thu=day(@TempsDate),Wed=day(@TempsDate)-1, Tue=day(@TempsDate)-2, Mon=day(@TempsDate)-3,Sun=day(@TempsDate)-4 where sl=1
        end
        else if(@var='Thu')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Wed=day(@TempsDate), Tue=day(@TempsDate)-1, Mon=day(@TempsDate)-2,Sun=day(@TempsDate)-3 where sl=1
        end
        else if(@var='Wed')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set  Tue=day(@TempsDate), Mon=day(@TempsDate)-1,Sun=day(@TempsDate)-2 where sl=1
        end
        else if(@var='Tue')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set   Mon=day(@TempsDate),Sun=day(@TempsDate)-1 where sl=1
        end
        else if(@var='Mon')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set   Sun=day(@TempsDate) where sl=1
        end

        -----------------------------End Previous-------------------------------------------------
end
set @SDate = DATEADD(DD,1,@SDate)

end

select  sl, case when Sun =0 then '' else Sun end Sun, case when Mon =0 then '' else Mon end Mon, case when Tue =0 then '' else Tue end Tue,
case when Wed =0 then '' else Wed end Wed,case when Thu =0 then '' else Thu end Thu,
case when Fri =0 then '' else Fri end Fri, case when Sat =0 then '' else Sat end Sat from #tblweek
drop table #tblweek

after executing the  above code  output like below.

  4th way to create calendar

  Now how to fill next month date in calendar
 declare @M int=1, @Y int=2016, @SDate date, @Edate date, @var varchar(50), @var1 varchar(50), @Q varchar(2500), @count int, @r int=0, @firstDay varchar(25)= 'Wed',@TempsDate date

set @SDate = Cast(cast(@Y as varchar) +'-'+cast(@M as varchar)+'-'+'01' as date)
set @TempsDate=@SDate
set @Edate= DATEADD(DD,-1, DATEADD(MM,1,@SDate))

create table #tblweek  ( sl int identity(1,1)  ,Sun varchar(10) default (0),Mon varchar(10) default (0),
Tue varchar(10) default (0),Wed varchar(10) default (0),Thu varchar(10) default (0),Fri varchar(10) default (0),
Sat varchar(10) default (0))
declare @tbl table(cnt int)


while(@SDate<=@Edate)
begin

set @var = datename(dw,@SDate)
set @var =LEFT(@var,3)
set @var1=day(@SDate)
set @Q = 'select count(*) from #tblweek where ['+@var+'] =0 '
delete @tbl
insert into @tbl
exec(@Q)

set @count = (select top 1 * from @tbl)

if(@count> 0)
begin
set @Q = 'select count(*) from #tblweek where [Sat] !=0 and sl = '''+cast(@r as varchar)+''''
delete @tbl
insert into @tbl
exec(@Q)
set @count = (select top 1 * from @tbl)

if(@count>0)
begin

set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
exec(@Q)
set @r = @r +1
end

else
begin
set @Q='update #tblweek set ['+@var+'] = '+@var1+' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
if(@var='Sat')
set @r = @r
end
end
else
begin
        set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
        exec(@Q)
        set @r = @r +1


        -------------------------- For Previous Month-----------------------
        if(@var='Sat')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Fri=day(@TempsDate),Thu=day(@TempsDate)-1,Wed=day(@TempsDate)-2, Tue=day(@TempsDate)-3, Mon=day(@TempsDate)-4,Sun=day(@TempsDate)-5 where sl=1
        end
        else if(@var='Fri')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Thu=day(@TempsDate),Wed=day(@TempsDate)-1, Tue=day(@TempsDate)-2, Mon=day(@TempsDate)-3,Sun=day(@TempsDate)-4 where sl=1
        end
        else if(@var='Thu')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set Wed=day(@TempsDate), Tue=day(@TempsDate)-1, Mon=day(@TempsDate)-2,Sun=day(@TempsDate)-3 where sl=1
        end
        else if(@var='Wed')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set  Tue=day(@TempsDate), Mon=day(@TempsDate)-1,Sun=day(@TempsDate)-2 where sl=1
        end
        else if(@var='Tue')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set   Mon=day(@TempsDate),Sun=day(@TempsDate)-1 where sl=1
        end
        else if(@var='Mon')
        begin
        set @TempsDate= DATEADD(DD,-1,@TempsDate)
        Update #tblweek set   Sun=day(@TempsDate) where sl=1
        end

        -----------------------------End Previous-------------------------------------------------


end



set @SDate = DATEADD(DD,1,@SDate)

end



-------------------------------For Next Month------------------------------------
declare @max int=0

set @max= (select max(sl) from #tblweek)
set @var = left(datename(dw,@Edate),3)

        if(@var='Fri')
        begin
  
        Update #tblweek set Sat=1 where sl=@max
        end
        else if(@var='Thu')
        begin
  
        Update #tblweek set Sat=2, Fri=1 where sl=@max
        end
        else if(@var='Wed')
        begin
      
        Update #tblweek set Sat=3, Fri=2,Thu=1 where sl=@max
        end
        else if(@var='Tue')
        begin
      
        Update #tblweek set Sat=4, Fri=3,Thu=2,Wed=1 where sl=@max
        end
        else if(@var='Mon')
        begin
      
        Update #tblweek set Sat=5, Fri=4,Thu=3,Wed=2,Tue=1 where sl=@max
        end
        else if(@var='Sun')
        begin
      
        Update #tblweek set Sat=6, Fri=5,Thu=4,Wed=3,Tue=2,Mon=1 where sl=@max
        end

-------------------------End Next Month-----------------------------------


 select   case when Sun =0 then '' else Sun end Sun, case when Mon =0 then '' else Mon end Mon, case when Tue =0 then '' else Tue end Tue,
case when Wed =0 then '' else Wed end Wed,case when Thu =0 then '' else Thu end Thu,
case when Fri =0 then '' else Fri end Fri, case when Sat =0 then '' else Sat end Sat from #tblweek
drop table #tblweek

OR 
using while loop
 




declare @M int=8, @Y int=2015, @SDate date, @Edate date, @var varchar(50), @var1 varchar(50), @Q varchar(2500), @count int, @r int=0

set @SDate = Cast(cast(@Y as varchar) +'-'+cast(@M as varchar)+'-'+'01' as date)
set @Edate= DATEADD(DD,-1, DATEADD(MM,1,@SDate))

create table #tblweek  ( sl int identity(1,1)  ,Sun varchar(10) default (0),Mon varchar(10) default (0),Tue varchar(10) default (0),Wed varchar(10)
default (0),Thu varchar(10) default (0),Fri varchar(10) default (0),Sat varchar(10) default (0))
declare @tbl table(cnt int)


while(@SDate<=@Edate)
begin

set @var = datename(dw,@SDate)
set @var =LEFT(@var,3)
set @var1=day(@SDate)
set @Q = 'select count(*) from #tblweek where ['+@var+'] =0 '
delete @tbl
insert into @tbl
exec(@Q)

set @count = (select top 1 * from @tbl)

if(@count> 0)
begin
set @Q = 'select count(*) from #tblweek where [Sat] !=0 and sl = '''+cast(@r as varchar)+''''
delete @tbl
insert into @tbl
exec(@Q)
set @count = (select top 1 * from @tbl)

if(@count>0)
begin

set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
exec(@Q)
set @r = @r +1
end

else
begin
set @Q='update #tblweek set ['+@var+'] = '+@var1+' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
if(@var='Sat')
set @r = @r
end
end
else
begin
set @Q='insert into #tblweek (['+@var+']) values ('+@var1+')'
exec(@Q)
set @r = @r +1
end
set @SDate = DATEADD(DD,1,@SDate)
end


-----------for previous------------------------
declare @SDate1 date,@Date1 int,@num1 int
set @SDate1 = Cast(cast(@Y as varchar) +'-'+cast(@M as varchar)+'-'+'01' as date)

set @var = datename(dw,@SDate1)
set @SDate1=DATEADD(MM,-1,@SDate1)
set @SDate1= DATEADD(DD,-1, DATEADD(MM,1,@SDate1))
set @var =LEFT(@var,3)
set @var1=day(@SDate)
if(@var='Mon')
begin
set @num1=1
while(@num1>0)
begin

set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'

set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end
if(@var='Tue')
begin
set @num1=2
while(@num1>0)
begin
set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'
set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end
if(@var='Wed')
begin
set @num1=3
set @Date1=(select right(@SDate1,2))
while(@num1>0)
begin
set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'
set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end
if(@var='Thu')
begin
set @num1=4
set @Date1=(select right(@SDate1,2))
while(@num1>0)
begin
set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'
set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end
if(@var='Fri')
begin
set @num1=5
set @Date1=(select right(@SDate1,2))
while(@num1>0)
begin
set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'
set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end
if(@var='Sat')
begin
set @num1=6
while(@num1>0)
begin

set @Date1=(select right(@SDate1,2))
set @var = datename(dw,@SDate1)
set @var =LEFT(@var,3)
set @r='1'
set @Q='update #tblweek set ['+@var+'] = '''+cast(@Date1 as varchar )+''' where sl = '''+cast(@r as varchar)+''''
exec(@Q)
set @SDate1=DATEADD(DD,-1,@SDate1)
set @num1=@num1-1
end
end

-----------end previous------------------------

---------------for next--------------------------
declare @max int=0

set @max= (select max(sl) from #tblweek)
set @var = left(datename(dw,@Edate),3)

        if(@var='Fri')
        begin
   
        Update #tblweek set Sat=1 where sl=@max
        end
        else if(@var='Thu')
        begin
   
        Update #tblweek set Sat=2, Fri=1 where sl=@max
        end
        else if(@var='Wed')
        begin
       
        Update #tblweek set Sat=3, Fri=2,Thu=1 where sl=@max
        end
        else if(@var='Tue')
        begin
       
        Update #tblweek set Sat=4, Fri=3,Thu=2,Wed=1 where sl=@max
        end
        else if(@var='Mon')
        begin
       
        Update #tblweek set Sat=5, Fri=4,Thu=3,Wed=2,Tue=1 where sl=@max
        end
        else if(@var='Sun')
        begin
       
        Update #tblweek set Sat=6, Fri=5,Thu=4,Wed=3,Tue=2,Mon=1 where sl=@max
        end

--------------end next-------------------------
select case when Sun =0 then '' else Sun end Sun, case when Mon =0 then '' else Mon end Mon, case when Tue =0 then '' else Tue end Tue,
case when Wed =0 then '' else Wed end Wed,case when Thu =0 then '' else Thu end Thu,
case when Fri =0 then '' else Fri end Fri, case when Sat =0 then '' else Sat end Sat from #tblweek
drop table #tblweek



Output wii be: