Month, Year and Calendar

>>Script Language and Platform: MS SQL 2000

This sproc was created as an excercise in tSql date calculations and the new table variable feature or MSSQL2K. This sproc expects a month number and year, and returns two recordsets.

RS1 = [month name], [year]
RS2 = A calendar representation of the days in the month number passed into this sproc.

For now this sproc has really only served as entertainment to me.

Author: [email protected]


/* WHAT *******************************************************************************************
* This is an excercise using date functions and scripting to create a simple calendar
* This sproc takes a month and year in numerical form and returns a calendar in the results
* USAGE : EXEC efCalendar 1,2002 = January 2002
**************************************************************************************************/

/*************************************************************************************************/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE efCalendar @iMo nvarchar(2), @iYr nvarchar(4)

AS

SET NOCOUNT ON
/*************************************************************************************************/

/* DECLARE VARS **********************************************************************************/
DECLARE @iCol int, @sql varchar(30),
@iFirstDay int, @iOffset int,
@iNumDays int, @iDay nvarchar(30),
@dThisMo datetime, @dNextMo datetime,
@sMoName varchar(15)
/*************************************************************************************************/

/* SET VARS **************************************************************************************/
SELECT @iCol = 1
SELECT @sql = ”
/*************************************************************************************************/

/* DO MONTH CALCULATIONS *************************************************************************/
–get date object for current month
SELECT @dThisMo = CAST(@iMo + ‘/1/’ + @iYr AS smalldatetime)
–get month name
SELECT @sMoName = DATENAME ( mm , @dThisMo )
–get next month
SELECT @dNextMo = DATEADD(mm, 1, @dThisMo)
–get number of days in current month
SELECT @iNumDays = DATEPART (dd, DATEADD(dd, -1, @dNextMo))
–get weekday of current month day 1
SELECT @iFirstDay = DATEPART (dw, @dThisMo)
/*************************************************************************************************/

/* CREATE TEMP TABLE *****************************************************************************/
CREATE TABLE #calendar (
sun INT DEFAULT 0 NULL,
mon INT DEFAULT 0 NULL,
tue INT DEFAULT 0 NULL,
wed INT DEFAULT 0 NULL,
thu INT DEFAULT 0 NULL,
fri INT DEFAULT 0 NULL,
sat INT DEFAULT 0 NULL
)
/*************************************************************************************************/

/* DO CALENDAR LOOP ******************************************************************************/
WHILE @iCol <= 42 --42 squares in cal table BEGIN -- calculate offset for month --1:0 IF(@iFirstDay) = 1 BEGIN SELECT @iOffset = 0 END ELSE --2:-1 IF(@iFirstDay) = 2 BEGIN SELECT @iOffset = -1 END ELSE --3:-2 IF(@iFirstDay) = 3 BEGIN SELECT @iOffset = -2 END ELSE --4:-3 IF(@iFirstDay) = 4 BEGIN SELECT @iOffset = -3 END ELSE --5:-4 IF(@iFirstDay) = 5 BEGIN SELECT @iOffset = -4 END ELSE --6:-5 IF(@iFirstDay) = 6 BEGIN SELECT @iOffset = -5 END ELSE --7:-6 IF(@iFirstDay) = 7 BEGIN SELECT @iOffset = -6 END --adjust column using offset SELECT @iDay = @iCol + @iOffset --check for days less than start day IF (@iDay < 0) BEGIN SELECT @iDay = 0 END --check for numbers greater than days in month IF (@iDay > @iNumDays)
BEGIN
SELECT @iDay = 0
END

–build number list to insert
SELECT @sql = CAST(@sql + @iDay + ‘,’ AS varchar(30))

–check for saturday(day 7)
IF (@iCol % 7) = 0
BEGIN
–drop the trailing comma
SELECT @sql = LEFT(@sql, (LEN(@sql) – 1) )
–insert record into table
EXEC(‘INSERT INTO #calendar VALUES(‘ + @sql + ‘)’)
–clear statement for next loop
SELECT @sql = ”
END

SELECT @iCol = @iCol + 1
END
/************************************************************************************/

/*SHOW RESULTS **********************************************************************/
SELECT @sMoName AS ‘month’, @iYr AS ‘year’
SELECT * FROM #calendar
/************************************************************************************/

/* DROP TEMP TABLE ******************************************************************/
DROP TABLE #calendar

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/************************************************************************************/

EXEC efCalendar 3,2003
–DROP PROCEDURE efCalendar



Disclaimer:
We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home

Latest Articles