SQLServerCentral / Article Discussions / Discuss content posted by vinaykumar / Article Discussions by Author / Print The Calender 2 / Latest Posts

RE: Print The Calender 2

rob.patterson-1085747 — Tue, 14 Aug 2012 14:24:20 GMT

After some digging I found one I had done quite time ago. In my opinion, it has a little nicer output. Also, I didn't realize about the every 400 years skipping leap year, but mine actually handled that just fine.Enjoy!Here's the code:[code="other"]DECLARE @thisyear varchar(4),@cnt int,@datestr varchar(12)SET @thisyear = '2100'SET nocount onIF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendarCREATE TABLE #calendar (cid int identity(0,1),col1 varchar(4),col2 varchar(4),col3 varchar(4),col4 varchar(4),col5 varchar(4),col6 varchar(4),col7 varchar(4),col8 varchar(4),col9 varchar(4),col10 varchar(4),col11 varchar(4),col12 varchar(4),col13 varchar(4),col14 varchar(4),col15 varchar(4),col16 varchar(4),col17 varchar(4),col18 varchar(4),col19 varchar(4),col20 varchar(4),col21 varchar(4),col22 varchar(4),col23 varchar(4))INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)VALUES ('','','','','','','','','','','',@thisyear,'','','','','','','','','','','')SELECT @cnt = 1WHILE @cnt < 12BEGIN INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT '','','',convert(varchar(3),(cast((cast(@cnt as varchar(2)) + '/01/' + @thisyear) as datetime)),100),'','','','','','','',convert(varchar(3),(cast((cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear) as datetime)),100),'','','','','','','',convert(varchar(3),(cast((cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear) as datetime)),100),'','','' INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) VALUES ('Sun','Mon','Tue','Wed','Thu','Fri','Sat','' ,'Sun','Mon','Tue','Wed','Thu','Fri','Sat','','Sun','Mon','Tue','Wed','Thu','Fri','Sat') INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '1' else '' END as 'Col1' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '2' when 2 then '1' else '' END as 'Col2' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '3' when 2 then '2' when 3 then '1' else '' END as 'Col3' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '4' when 2 then '3' when 3 then '2' when 4 then '1' else '' END as 'Col4' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '5' when 2 then '4' when 3 then '3' when 4 then '2' when 5 then '1' else '' END as 'Col5' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '6' when 2 then '5' when 3 then '4' when 4 then '3' when 5 then '2' when 6 then '1' else '' END as 'Col6' ,case datepart(dw,(cast(cast(@cnt as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '7' when 2 then '6' when 3 then '5' when 4 then '4' when 5 then '3' when 6 then '2' when 7 then '1' else '' END as 'Col7' ,'' as 'Col8' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '1' else '' END as 'Col9' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '2' when 2 then '1' else '' END as 'Col10' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '3' when 2 then '2' when 3 then '1' else '' END as 'Col11' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '4' when 2 then '3' when 3 then '2' when 4 then '1' else '' END as 'Col12' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '5' when 2 then '4' when 3 then '3' when 4 then '2' when 5 then '1' else '' END as 'Col13' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '6' when 2 then '5' when 3 then '4' when 4 then '3' when 5 then '2' when 6 then '1' else '' END as 'Col14' ,case datepart(dw,(cast(cast(@cnt+1 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '7' when 2 then '6' when 3 then '5' when 4 then '4' when 5 then '3' when 6 then '2' when 7 then '1' else '' END as 'Col15' ,'' as 'Col16' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '1' else '' END as 'Col17' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '2' when 2 then '1' else '' END as 'Col18' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '3' when 2 then '2' when 3 then '1' else '' END as 'Col19' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '4' when 2 then '3' when 3 then '2' when 4 then '1' else '' END as 'Col20' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '5' when 2 then '4' when 3 then '3' when 4 then '2' when 5 then '1' else '' END as 'Col21' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '6' when 2 then '5' when 3 then '4' when 4 then '3' when 5 then '2' when 6 then '1' else '' END as 'Col122' ,case datepart(dw,(cast(cast(@cnt+2 as varchar(2)) + '/01/' + @thisyear as datetime))) when 1 then '7' when 2 then '6' when 3 then '5' when 4 then '4' when 5 then '3' when 6 then '2' when 7 then '1' else '' END as 'Col123' INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT col7+1,col7+2,col7+3,col7+4,col7+5,col7+6,col7+7,'',col15+1,col15+2,col15+3,col15+4,col15+5,col15+6,col15+7,'',col23+1,col23+2,col23+3,col23+4,col23+5,col23+6,col23+7 FROM #calendar WHERE cid = (SELECT max(cid) FROM #calendar) INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT col7+1,col7+2,col7+3,col7+4,col7+5,col7+6,col7+7,'',col15+1,col15+2,col15+3,col15+4,col15+5,col15+6,col15+7,'',col23+1,col23+2,col23+3,col23+4,col23+5,col23+6,col23+7 FROM #calendar WHERE cid = (SELECT max(cid) FROM #calendar) INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT col7+1,col7+2,col7+3,col7+4,col7+5,col7+6,col7+7,'',col15+1,col15+2,col15+3,col15+4,col15+5,col15+6,col15+7,'',col23+1,col23+2,col23+3,col23+4,col23+5,col23+6,col23+7 FROM #calendar WHERE cid = (SELECT max(cid) FROM #calendar) INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+1 as varchar(2)) END else '' END as col1 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+2 as varchar(2)) END else '' END as col2 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+3 as varchar(2)) END else '' END as col3 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+4 as varchar(2)) END else '' END as col4 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+5 as varchar(2)) END else '' END as col5 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+6 as varchar(2)) END else '' END as col6 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+7 as varchar(2)) END else '' END as col7 ,'' as col8 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+1 as varchar(2)) END else '' END as col9 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+2 as varchar(2)) END else '' END as col10 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+3 as varchar(2)) END else '' END as col11 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+4 as varchar(2)) END else '' END as col12 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+5 as varchar(2)) END else '' END as col13 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+6 as varchar(2)) END else '' END as col14 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+7 as varchar(2)) END else '' END as col15 ,'' as col16 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+1 as varchar(2)) END else '' END as col17 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+2 as varchar(2)) END else '' END as col18 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+3 as varchar(2)) END else '' END as col19 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+4 as varchar(2)) END else '' END as col20 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+5 as varchar(2)) END else '' END as col21 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+6 as varchar(2)) END else '' END as col22 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+7 as varchar(2)) END else '' END as col23 FROM #calendar WHERE cid = (SELECT max(cid) FROM #calendar) INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) SELECT case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+1 as varchar(2)) END else '' END as col1 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+2 as varchar(2)) END else '' END as col2 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+3 as varchar(2)) END else '' END as col3 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+4 as varchar(2)) END else '' END as col4 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+5 as varchar(2)) END else '' END as col5 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+6 as varchar(2)) END else '' END as col6 ,case when col7 <> '' then case isdate((cast(@cnt as varchar(2)) + '/' + cast(col7+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col7+7 as varchar(2)) END else '' END as col7 ,'' as col8 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+1 as varchar(2)) END else '' END as col9 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+2 as varchar(2)) END else '' END as col10 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+3 as varchar(2)) END else '' END as col11 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+4 as varchar(2)) END else '' END as col12 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+5 as varchar(2)) END else '' END as col13 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+6 as varchar(2)) END else '' END as col14 ,case when col15 <> '' then case isdate((cast(@cnt+1 as varchar(2)) + '/' + cast(col15+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col15+7 as varchar(2)) END else '' END as col15 ,'' as col16 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+1 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+1 as varchar(2)) END else '' END as col17 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+2 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+2 as varchar(2)) END else '' END as col18 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+3 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+3 as varchar(2)) END else '' END as col19 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+4 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+4 as varchar(2)) END else '' END as col20 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+5 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+5 as varchar(2)) END else '' END as col21 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+6 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+6 as varchar(2)) END else '' END as col22 ,case when col23 <> '' then case isdate((cast(@cnt+2 as varchar(2)) + '/' + cast(col23+7 as varchar(2)) + '/' + @thisyear)) when 0 then '' else cast(col23+7 as varchar(2)) END else '' END as col23 FROM #calendar WHERE cid = (SELECT max(cid) FROM #calendar) INSERT INTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23) VALUES ('','','','','','','','','','','','','','','','','','','','','','','') SET @cnt = @cnt + 3ENDSELECT col1 as ' ',col2 as ' ',col3 as ' ',col4 as ' ',col5 as ' ',col6 as ' ',col7 as ' ',col8 as ' ',col9 as ' ',col10 as ' ',col11 as ' ',col12 as ' ',col13 as ' ',col14 as ' ',col15 as ' ',col16 as ' ',col17 as ' ',col18 as ' ',col19 as ' ',col20 as ' ',col21 as ' ',col22 as ' ',col23 as ' 'FROM #calendarIF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar[/code]

RE: Print The Calender 2

Jack B. — Tue, 14 Aug 2012 06:19:37 GMT

I personaly cringe when I see manual calculations involving date/time. It's way to easy to mess up and there are usualy good library functions for it.In T-SQL would I for instance do something like this[code]SELECT DATEPART( dd, DATEADD( dd, -1, DATEADD( mm, 1, CONVERT(varchar(8), @Date, 20) + '01' ) ) ) AS [DaysInMonth][/code]to get the numer of days in the current month. That way are you safe with all leap years and don't have to worry about a thing.

RE: Print The Calender 2

Andrew Diniz — Tue, 14 Aug 2012 05:40:51 GMT

Indeed.You can eliminate 7 evaluations from the vast majority of leap year candidates by simplifying and reordering the predicates:[code]SELECT CASE WHEN @CurrentMonth in (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN @CurrentMonth in (4, 6, 9, 11) THEN 30 -- only '@CurrentMonth = 2' left WHEN @YearNo % 4 > 0 THEN 28 -- not divisible by 4 => not leap year -- only years divisible by 4 left; century leap years must be divisible by 400 WHEN @YearNo % 100 > 0 THEN 29 -- not a century year => leap year WHEN @YearNo % 400 = 0 THEN 29 -- is divisible by 400 => leap year ELSE 28 END AS DaysInMonth;[/code]Admitedly, in this application where we're evaluating DaysInMonth only once, the loss of code clarity doesn't justify the simplification. If you were applying this DaysInMonth function to a large volume of currentMonth values, however, it would be worth considering.

RE: Print The Calender 2

Luis Cazares — Mon, 13 Aug 2012 15:11:36 GMT

[quote][b]timothy.shawley (8/13/2012)[/b][hr]One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.Otherwise the script looks like it works like a charm...[/quote]That's what I saw, but it can easily be corrected if you add two conditions to the CASE[code="sql"]set @TotalMonthDays = case when @currentMonth in (1,3,5,7,8,10,12) then 31 when @currentMonth in (4,6,9,11) then 30 when @currentMonth = 2 and @YearNo % 400 = 0 then 29 when @currentMonth = 2 and @YearNo % 100 = 0 then 28 when @currentMonth = 2 and @YearNo % 4 = 0 then 29 when @currentMonth = 2 and @YearNo % 4 > 0 then 28[/code]

RE: Print The Calender 2

timothy.shawley — Mon, 13 Aug 2012 12:49:44 GMT

One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.Otherwise the script looks like it works like a charm...

RE: Print The Calender 2

hannes.malan — Mon, 13 Aug 2012 05:59:13 GMT

hehehe.. , so far ...I have no idea... I just like the odd script that is off the beaten track:-Pto expound a bit further......one might not use the script it was written for, but at times you get to a situation where you need similar logic to solve some kind of bussiness need.

RE: Print The Calender 2

Jeff Moden — Mon, 13 Aug 2012 05:44:50 GMT

[quote][b]hannes.malan (8/13/2012)[/b][hr]I really liked your calendar idea, so I amended it a bit for my use.[/quote]I have to ask... what will you actually end up using this for? Not challenging what you're doing. I just really want to know.

RE: Print The Calender 2

hannes.malan — Mon, 13 Aug 2012 05:29:49 GMT

I really liked your calendar idea, so I amended it a bit for my use..[code="other"]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar2print]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].calendar2printGO/***************************************************************************************Created By :- Vinay KumarCreated on :- 29 Jan 2008 EXEC calender '01/01/2016'Purpose :- Print the calender from the given Date until end of current year------------------------------------Amended by Hannes MalanAmended On :- Aug 13, 2012 EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1Purpose :- Print the calender from the given Date ... ..and if @StartDate is null, use current date (@StartDate) ..also from specified date or from first day of specified month (@fullmonth) ..for a certain amount of months (@months)****************************************************************************************/Create Procedure calendar2print @StartDate DateTime ,@fullmonth int = 1 ,@months int = 1Asset nocount onif @StartDate is null set @StartDate = getdate()if (@fullmonth = 1) begin set @StartDate = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)endset datefirst 7DECLARE @tbl Table(RowID int identity(1,1), Sun varchar(20) default '', Mon varchar(20) default '', Tue varchar(20) default '', Wed varchar(20) default '', Thu varchar(20) default '', Fri varchar(20) default '', Sat varchar(20) default '')declare @monthCount int set @monthCount = 1DECLARE @currentMonth intDECLARE @CurrentDate intDECLARE @CurrentDay intDECLARE @CurrentRowID intDECLARE @TotalMonthDays intDECLARE @monthName varchar(20)set @CurrentDate = datepart(dd, @StartDate)set @currentMonth = datepart(mm, @StartDate)set @CurrentDay = datepart(dw, @StartDate)insert @tbl(Mon) values ('')set @CurrentRowID = @@Identitywhile @monthCount <= @months begin set @monthCount = @monthCount + 1 set @TotalMonthDays = case when month(@StartDate) in (1,3,5,7,8,10,12) then 31 when month(@StartDate) in (4,6,9,11) then 30 when month(@StartDate) = 2 and year(@StartDate) % 4 = 0 then 29 when month(@StartDate) = 2 and year(@StartDate) % 4 <> 0 then 28 end set @monthName = datename(m, @StartDate) insert @tbl(Tue, Thu) values (@monthName, Convert(Varchar, year(@StartDate))) set @CurrentRowID = @@Identity insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat) values ('_______', '_______', '_______', '_______', '_______', '_______', '_______') insert @tbl(Sun) values ('') set @CurrentRowID = @@Identity while @CurrentDate <= @TotalMonthDays begin if @CurrentDay = 1 begin insert @tbl(Sun) values ('') set @CurrentRowID = @@Identity end if @CurrentDay = 1 update @tbl set Sun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 2 update @tbl set Mon = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 3 update @tbl set Tue = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 4 update @tbl set wed = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 5 update @tbl set Thu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 6 update @tbl set Fri = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID else if @CurrentDay = 7 update @tbl set Sat = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID set @CurrentDate = @CurrentDate + 1 if @CurrentDate = @TotalMonthDays + 1 begin IF (Datepart(DD,@StartDate)>1) set @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate) set @StartDate = DATEADD(Month,1,@StartDate) set @CurrentDay = datepart(dw, @StartDate) insert @tbl(Sun,Mon,Tue,Wed,Thu,Fri,Sat) values ('*******', '*******', '*******', '*******', '*******', '*******', '*******') end else begin set @CurrentDay = (@CurrentDay + 1) % 8 if (@CurrentDay = 0) set @CurrentDay = 1 end end set @CurrentDate = 1end-- Finaly show the outputselect Sun, Mon, Tue, Wed, Thu, Fri, Sat from @tbl where Mon <> '' or Tue <> '' or Wed <> '' or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''[/code]

RE: Print The Calender 2

Arjen Krap — Fri, 10 Aug 2012 23:44:36 GMT

Another way to print a calendar using PIVOT:[code="sql"]CREATE FUNCTION CalenderUsingPivot( @BeginDate date , @EndDate date)RETURNS @Calendar TABLE( Week tinyint , Monday tinyint , Tuesday tinyint , Wednesday tinyint , Thursday tinyint , Friday tinyint , Saturday tinyint , Sunday tinyint)ASBEGIN DECLARE @date TABLE ( Date date , Day AS DAY(Date) , Week AS DATEPART(week, Date) , Weekday AS DATENAME(weekday, Date) ); DECLARE @i date; SET @i = @BeginDate; WHILE @i <= @EndDate BEGIN INSERT @date VALUES (@i); SET @i = DATEADD(day, 1, @i); END; INSERT @Calendar SELECT Week , p.Monday , p.Tuesday , p.Wednesday , p.Thursday , p.Friday , p.Saturday , p.Sunday FROM ( SELECT Day, Week, Weekday FROM @date ) AS c PIVOT ( MAX(Day) FOR Weekday IN ( Monday , Tuesday , Wednesday , Thursday , Friday , Saturday , Sunday ) ) AS p ORDER BY Week; RETURN;END;GOSET LANGUAGE English;SELECT *FROM dbo.CalenderUsingPivot('2012-01-01', '2012-12-31');[/code]

RE: Print The Calender 2

ACinKC — Fri, 10 Aug 2012 13:32:27 GMT

^^^^^HA! That's exactly what I was thinking! CALENDAR <> CALENDER([size="1"]Maybe he drives around in a Camero, too...[/size])

RE: Print The Calender 2

Andrew Diniz — Fri, 10 Aug 2012 04:03:07 GMT

A calender is a series of hard pressure rollers used to form or smooth a sheet of material (see http://en.wikipedia.org/wiki/Calender).Not sure how this relates to dates. :hehe:

RE: Print The Calender 2

Jack B. — Fri, 10 Aug 2012 02:44:34 GMT

You are a bit US centric... ;-) Should have "SET DATEFIRST 7" in there if you want to make sure that your script runs correctly everywhere (or take @@DATEFIRST into account when identifiying the given day of week).