Calendar Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Calendar Script

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-19 : 04:04:04

It generate the calender for the year!

I hope this will help u for various Date related Problems....

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*

CALENDAR 2009

*/

ALTER PROCEDURE CALENDAR (@YEAR INT) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=1

WHILE @STARTMONTH<=12

BEGIN

SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)

SELECT datename(month,@DATE) as Monthname
--print @DATE

SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATENAME(WEEK,@DATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1

WHILE @wkcount<= @weeksinmonth

begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
SET @WKCOUNT=@WKCOUNT + 1
end

WHILE @COUNT<=@MONTHDAYCOUNT

BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1

END
--select * from #TEMP

SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)

SET @COUNT=@COUNT + 1
END

UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'

--select * from #TEMP
SELECT SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY FROM #TEMP
DROP TABLE #TEMP
SET @INPUTDATE=DATEADD(MM,1,@INPUTDATE)
SET @STARTMONTH=@STARTMONTH+1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards

Senthil.C
----------------------------------------------------------------------------
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources