Month, Year and Calendar

April 20, 2004



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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers