1. Run this query
-----code:start----------------------------------------------------------
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Show_Calendar]')
AND type in (N'P', N'PC')
)
DROP PROCEDURE [dbo].[Show_Calendar]
GO
/****************************************************
Purpose :- Print the calendar from the given Date
******************************************************/
Create Procedure Show_Calendar
@StartDate DateTime='01/01/2012' ----Default Value
As
Set nocount on
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 @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)
DECLARE @YearNo int
set @YearNo = datepart(yy, @StartDate)
set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Mon) values ('')
set @CurrentRowID = @@Identity
while @currentMonth <= 12
begin
set @monthName = datename(m, @StartDate)
insert @tbl(Tue,Wed, Thu) values (@monthName, ' xxx ', Convert(Varchar, @YearNo))
set @CurrentRowID = @@Identity
insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat)
values ( '________', '________'
, '________', '________'
, '________', '________', '________'
)
insert @tbl(Sun) values ('')
set @CurrentRowID = @@Identity
set @TotalMonthDays
= case when @currentMonth in (1,3,5,7,8,10,12) then 31
when @currentMonth in (4,6,9,11) then 30
when @currentMonth = 2 and @YearNo % 4 = 0 then 29
when @currentMonth = 2 and @YearNo % 4 <> 0 then 28
end
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
SET @currentMonth = @currentMonth + 1
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
set @CurrentDay = (@CurrentDay + 1) % 8
if (@CurrentDay = 0)
SET @CurrentDay = 1
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 <> ''
-----code:end----------------------------------------------------------
2. Try to run the procedure
A. With Default Date Value
-----code:start----------------------
EXEC [Show_Calendar]
GO
-----code:end------------------------
B. With Specific Date
-----code:start----------------------
EXEC [Show_Calendar] '11/20/2012'
GO
-----code:end------------------------
3. Optional: For remove the procedure
-----code:start----------------------
DROP PROCEDURE [Show_Calendar]
GO
-----code:end------------------------
Source:
http://www.sqlservercentral.com/scripts/T-Sql+Fun/92431/
Article : Print The Calendar 2
By Vinay Kumar