Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Apr 20, 2004

Month, Year and Calendar

By DatabaseJournal.com Staff



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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM