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'
SETnocount on
IFEXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar
CREATETABLE #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))
INSERTINTO #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
INSERTINTO #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),'','',''
INSERTINTO #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')
INSERTINTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)
SELECTcase 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'
INSERTINTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)
SELECTcol7+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
WHEREcid = (SELECT max(cid) FROM #calendar)
INSERTINTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)
SELECTcol7+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
WHEREcid = (SELECT max(cid) FROM #calendar)
INSERTINTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)
SELECTcol7+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
WHEREcid = (SELECT max(cid) FROM #calendar)
INSERTINTO #calendar (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23)
SELECTcase 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
WHEREcid = (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)
SELECTcase 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
WHEREcid = (SELECT max(cid) FROM #calendar)
INSERTINTO #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
SELECTcol1 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
IFEXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar