CREATE FUNCTION CalenderUsingPivot( @BeginDate date , @EndDate date)RETURNS @Calendar TABLE( Week tinyint , Monday tinyint , Tuesday tinyint , Wednesday tinyint , Thursday tinyint , Friday tinyint , Saturday tinyint , Sunday tinyint)ASBEGIN DECLARE @date TABLE ( Date date , Day AS DAY(Date) , Week AS DATEPART(week, Date) , Weekday AS DATENAME(weekday, Date) ); DECLARE @i date; SET @i = @BeginDate; WHILE @i <= @EndDate BEGIN INSERT @date VALUES (@i); SET @i = DATEADD(day, 1, @i); END; INSERT @Calendar SELECT Week , p.Monday , p.Tuesday , p.Wednesday , p.Thursday , p.Friday , p.Saturday , p.Sunday FROM ( SELECT Day, Week, Weekday FROM @date ) AS c PIVOT ( MAX(Day) FOR Weekday IN ( Monday , Tuesday , Wednesday , Thursday , Friday , Saturday , Sunday ) ) AS p ORDER BY Week; RETURN;END;GOSET LANGUAGE English;SELECT *FROM dbo.CalenderUsingPivot('2012-01-01', '2012-12-31');
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar2print]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].calendar2printGO/***************************************************************************************Created By :- Vinay KumarCreated on :- 29 Jan 2008 EXEC calender '01/01/2016'Purpose :- Print the calender from the given Date until end of current year------------------------------------Amended by Hannes MalanAmended On :- Aug 13, 2012 EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1Purpose :- Print the calender from the given Date ... ..and if @StartDate is null, use current date (@StartDate) ..also from specified date or from first day of specified month (@fullmonth) ..for a certain amount of months (@months)****************************************************************************************/Create Procedure calendar2print @StartDate DateTime ,@fullmonth int = 1 ,@months int = 1Asset nocount onif @StartDate is null set @StartDate = getdate()if (@fullmonth = 1) begin set @StartDate = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)endset datefirst 7DECLARE @tbl Table(RowID int identity(1,1), Sun varchar(20) default '', Mon varchar(20) default '', Tue varchar(20) default '', Wed varchar(20) default '', Thu varchar(20) default '', Fri varchar(20) default '', Sat varchar(20) default '')declare @monthCount int set @monthCount = 1DECLARE @currentMonth intDECLARE @CurrentDate intDECLARE @CurrentDay intDECLARE @CurrentRowID intDECLARE @TotalMonthDays intDECLARE @monthName varchar(20)set @CurrentDate = datepart(dd, @StartDate)set @currentMonth = datepart(mm, @StartDate)set @CurrentDay = datepart(dw, @StartDate)insert @tbl(Mon) values ('')set @CurrentRowID = @@Identitywhile @monthCount <= @months begin set @monthCount = @monthCount + 1 set @TotalMonthDays = case when month(@StartDate) in (1,3,5,7,8,10,12) then 31 when month(@StartDate) in (4,6,9,11) then 30 when month(@StartDate) = 2 and year(@StartDate) % 4 = 0 then 29 when month(@StartDate) = 2 and year(@StartDate) % 4 <> 0 then 28 end set @monthName = datename(m, @StartDate) insert @tbl(Tue, Thu) values (@monthName, Convert(Varchar, year(@StartDate))) set @CurrentRowID = @@Identity insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat) values ('_______', '_______', '_______', '_______', '_______', '_______', '_______') insert @tbl(Sun) values ('') set @CurrentRowID = @@Identity while @CurrentDate <= @TotalMonthDays begin if @CurrentDay = 1 begin insert @tbl(Sun) values ('') set @CurrentRowID = @@Identity end if @CurrentDay = 1 update @tbl set Sun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 2 update @tbl set Mon = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 3 update @tbl set Tue = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 4 update @tbl set wed = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 5 update @tbl set Thu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 6 update @tbl set Fri = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 7 update @tbl set Sat = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID set @CurrentDate = @CurrentDate + 1 if @CurrentDate = @TotalMonthDays + 1 begin IF (Datepart(DD,@StartDate)>1) set @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate) set @StartDate = DATEADD(Month,1,@StartDate) set @CurrentDay = datepart(dw, @StartDate) insert @tbl(Sun,Mon,Tue,Wed,Thu,Fri,Sat) values ('*******', '*******', '*******', '*******', '*******', '*******', '*******') end else begin set @CurrentDay = (@CurrentDay + 1) % 8 if (@CurrentDay = 0) set @CurrentDay = 1 end end set @CurrentDate = 1end-- Finaly show the outputselect Sun, Mon, Tue, Wed, Thu, Fri, Sat from @tbl where Mon <> '' or Tue <> '' or Wed <> '' or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''