Print The Calendar 2

,

Hi friends,

In my early days, i have posted a simple sql script for print the calendar in sql server.

http://www.sqlservercentral.com/scripts/T-SQL/62091

few day ago, i was just checking this script and found some bugs. i corrected them.

This script remind me my early days in sql server.

Vinay

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


--- EXEC calendar '02/11/2011'
****************************************************************************************/

Create Procedure 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 <> ''

Rate

4.18 (11)

Share

Share

Rate

4.18 (11)