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
)
AS
BEGIN
	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;

GO

SET 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].calendar2print
GO

/***************************************************************************************
Created By :- Vinay Kumar
Created 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 Malan
Amended On :- Aug 13, 2012
	EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1
Purpose :- 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 = 1
As
set nocount on

if @StartDate is null set @StartDate = getdate()

if (@fullmonth = 1) begin
	set @StartDate  = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)
end

set datefirst 7
DECLARE @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 = 1
DECLARE @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)


set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)

insert @tbl(Mon) values ('')
set @CurrentRowID = @@Identity

while @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 = 1
end


-- Finaly show the output
select Sun, Mon, Tue, Wed, Thu, Fri, Sat 
from @tbl 
where  Mon <> '' or Tue <> '' or Wed <> ''  
	or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''