As I need to support French speaking persons, as I live/work in France, I modified for the date changes:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[calendar]
GO
/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 29 Jan 2008
Purpose :- Print the calendar from the given Date
Modified On :- July 20, 2012
Modified On :- June 24, 2013 - changed calendar representation for French
--- EXEC calendar '01/06/2013'
****************************************************************************************/
Create Procedure calendar
@StartDate DateTime='01/01/2012' ----Default Value
As
Set nocount on
DECLARE @tbl Table(RowID int identity(1,1), Dim varchar(20) default '', Lun varchar(20) default '', Mar varchar(20) default '',
Mer varchar(20) default '', Jeu varchar(20) default '', Ven varchar(20) default '', Sam 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(Lun) values ('')
set @CurrentRowID = @@Identity
while @currentMonth <= 12
begin
set @monthName = datename(m, @StartDate) ---upper(datename(m, @StartDate))
insert @tbl(Mar, Mer, Jeu) values (@monthName, ' xxx ', Convert(Varchar, @YearNo))
set @CurrentRowID = @@Identity
insert @tbl(Lun,Mar,Mer,Jeu,Ven,Sam,Dim) values ('________', '________', '________', '________', '________', '________', '________')
insert @tbl(Lun) 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(Lun) values ('')
set @CurrentRowID = @@Identity
end
if @CurrentDay = 1
update @tbl set Lun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 2
update @tbl set Mar = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 3
update @tbl set Mer = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 4
update @tbl set Jeu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 5
update @tbl set Ven = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 6
update @tbl set Sam = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 7
update @tbl set Dim = 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(Lun,Mar,Mer,Jeu,Ven,Sam,Dim) values ('************', '************', '************', '************', '************', '************', '************')
end
ELSE
set @CurrentDay = (@CurrentDay + 1) % 8
if (@CurrentDay = 0)
SET @CurrentDay = 1
end
set @CurrentDate = 1
end
-- Finaly show the output
select Lun, Mar, Mer, Jeu, Ven, Sam, Dim from @tbl where Lun <> '' or Mar <> '' or Mer <> '' or
Jeu <> '' or Ven <> '' or Sam <> '' or Dim <> ''