Print The Calender 2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by vinaykumar » Print The Calender 2

14 posts, Page 2 of 2

««1 2

Print The Calender 2

Rate Topic

Display Mode

Topic Options

Author

Message

Luis Cazares

Posted Monday, August 13, 2012 3:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:56 PM
Points: 1,086, Visits: 2,191

timothy.shawley (8/13/2012)

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

That's what I saw, but it can easily be corrected if you add two conditions to the CASE

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

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1344447

Andrew Diniz

Posted Tuesday, August 14, 2012 5:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:01 AM
Points: 191, Visits: 247

Indeed.

You can eliminate 7 evaluations from the vast majority of leap year candidates by simplifying and reordering the predicates:

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;

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.

Post #1344619

Jack B.

Posted Tuesday, August 14, 2012 6:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:20 AM
Points: 90, Visits: 70

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

SELECT
	DATEPART(
		dd,
		DATEADD(
			dd,
			-1,
			DATEADD(
				mm,
				1,
				CONVERT(varchar(8), @Date, 20) + '01'
			)
		)
	) AS [DaysInMonth]

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.

Post #1344646

rob.patterson-1085747

Posted Tuesday, August 14, 2012 2:24 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 9:00 AM
Points: 15, Visits: 64

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:

DECLARE	@thisyear varchar(4),@cnt int,@datestr varchar(12)

SET		@thisyear = '2100'

SET		nocount on

IF		EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar

CREATE	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 = 1

WHILE	@cnt < 12
BEGIN
		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 + 3
END

SELECT	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	#calendar

IF		EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar

Post #1345022

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions