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: eric@cronometric.com

/* 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