Monthly Calendar in SQL Server

Pandians
Posted by Pandians under Sql Server category on | Views : 4210
Introduction :
This code is used to generate Monthly Calendar for the MONTH & YEAR given / Current Month.

Code :
We have created stored procedure for the same.
IF OBJECT_ID('SQL_Calendar','P') IS NOT NULL
DROP PROC SQL_Calendar
GO
CREATE PROC SQL_Calendar
(
@Month INT = NULL ,
@Year INT = NULL
)AS
/********************************************************/
/* Created By : Pandian S */
/* Created On: 01-Oct-2009, Thursday */
/* Purpose : Month Calendar */
/* How to : EXEC SQL_Calendar <MONTH>,<YEAR>*/
/* : EXEC SQL_Calendar 10,2009 */
/********************************************************/
BEGIN
SET NOCOUNT ON

DECLARE @Serial INT ,
@Days INT ,
@Starts INT ,
@Concat VARCHAR(100),
@ActDate DATETIME,
@Date VARCHAR(11)

IF (@Month IS NULL AND @Year IS NULL)
BEGIN
SELECT @Month = MONTH(GETDATE())
SELECT @Year = YEAR(GETDATE())
END

IF (@Month IS NULL AND @Year IS NOT NULL)
SELECT @Month = MONTH(GETDATE())

IF (@Month IS NOT NULL AND @Year IS NULL)
SELECT @Year = YEAR(GETDATE())

IF (@Month <=0 OR @Month >12) RETURN

IF (@Year <= 1752)
BEGIN
PRINT 'Year Out-of-Range'
RETURN
END

IF (@Year = 9999 AND @Month>11)
BEGIN
PRINT 'Date Out-of-Range'
RETURN
END

SELECT @Starts = CASE DATENAME(DW,CAST(@Year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01') WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 END
SELECT @Concat = ''

SELECT @Date = CAST(@Year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01'
SELECT @Days = DATEDIFF(DD,@DATE,DATEADD(MM,1,@DATE))

SELECT @Serial = 1
PRINT 'Calendar : ' + DATENAME(MM,CAST(@Year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01') + ', ' + CAST(@Year AS VARCHAR) + CHAR(10) + Replicate('-',30) + CHAR(10)
PRINT ' S M T W T F S'
WHILE (@Serial <= @Days)
BEGIN

SELECT @Starts = CASE DATENAME(DW,CAST(@Year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-' + CAST(@Serial AS VARCHAR)) WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 END

IF (@Starts = 0)
IF (LEN(@Concat) =0)
SELECT @Concat = @Concat + ' ' + CAST(@Serial AS VARCHAR)
ELSE
SELECT @Concat = @Concat + CHAR(13) + CASE WHEN LEN(CAST(@Serial AS VARCHAR)) = 1 THEN ' ' ELSE '' END + CAST(@Serial AS VARCHAR)
ELSE
IF (LEN(@Concat) =0)
SELECT @Concat = @Concat + REPLICATE(CHAR(9),@Starts) + CASE WHEN LEN(CAST(@Serial AS VARCHAR)) = 1 THEN ' ' ELSE '' END + CAST(@Serial AS VARCHAR)
ELSE
SELECT @Concat = @Concat + CHAR(9) + CASE WHEN LEN(CAST(@Serial AS VARCHAR)) = 1 THEN ' ' ELSE '' END + CAST(@Serial AS VARCHAR)

SELECT @Serial = @Serial + 1
END

PRINT @Concat + CHAR(10)
END

Execute:
The SP can be executed in various ways

Calendar of October, 2009
EXEC SQL_Calendar 10,2009

Calendar of October ( Year is Optional )
EXEC SQL_Calendar @Month=10

Calendar of 2009 ( Month is Optional )
EXEC SQL_Calendar @Year=2009

Calendar of Current Month ( Month & Year is Optional )
EXEC SQL_Calendar

Result:
Calendar : October, 2009
------------------------------

S M T W T F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

Comments or Responses

Posted by: Sarvesh on: 10/3/2009 Level:Starter | Status: [Member]
hi pandians

I never ever thought this kind of logics/ideas. I realy enjoyed it.

I expecting more from you this kind of code & articals.

Each and every posts are identicaly Unique.

One more request, could you please post some sql server database administration related posts/code/articals ?

thank you
Sarvesh Sivam
Posted by: Pandians on: 10/4/2009 Level:Silver | Status: [Member] [MVP]
Hi Sarvesh
Good day. Thanks for your reply. We should convey our thanks to .NetFunda team to provided this kind of opportunities.
I am also planing for DBA level articles/posts.

Cheers


Posted by: Santosh4u on: 10/20/2009 Level:Bronze | Status: [Member]
thanx Pandians
how u made man,very nice calender.
then do one thing u give color in that Calender ??


Regards
Santosh

Login to post response