SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bernabe Diaz / Print Text Month Calendar / Latest Posts

RE: Print Text Month Calendar

diaz.bernabe — Wed, 09 Jan 2013 12:15:37 GMT

Bill, I like your version!Thanks.

RE: Print Text Month Calendar

Bill Talada — Fri, 28 Dec 2012 09:28:11 GMT

Here is a maintainable version...[code="other"]DECLARE @dt datetime, @DayOneDayOfWeek int, @DaysInMonth int, @s varchar(200), @crlf varchar(2);SET @dt = '20130401';SET @crlf = char(13) + char(10);-- get max days in month: calculate first day of the next month and subtract one daySET @DaysInMonth = (select datepart(day, dateadd(day, -1, dateadd(month, 1, cast(convert(varchar(8), @dt, 121)+'01' as datetime)))));-- need to know day one weekdaySET @DayOneDayOfWeek = (select DatePart(WeekDay, cast(convert(varchar(8), @dt, 121)+'01' as datetime)));-- build day numbers string and pad for first daySET @s = REPLACE(SUBSTRING(' << January YEAR >> << February YEAR >> << March YEAR >> << April YEAR >> << May YEAR >> << June YEAR >> << July YEAR >> << August YEAR >> << September YEAR >> << October YEAR >> << November YEAR >> << December YEAR >>', (DatePart(month, @dt) - 1) * 21 + 1, 21), 'YEAR', cast(DatePart(year, @dt) as varchar(4))) + ' Su Mo Tu We Th Fr Sa' + replicate(' ', @DayOneDayOfWeek - 1) + left(' 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31', @DaysInMonth * 3);-- Format for display by removing leading space and adding CRLF for each line.print SUBSTRING(@s, 2, 20) + @crlf + SUBSTRING(@s, 23, 20) + @crlf + SUBSTRING(@s, 44, 20) + @crlf + SUBSTRING(@s, 65, 20) + @crlf + SUBSTRING(@s, 86, 20) + @crlf + SUBSTRING(@s, 107, 20) + @crlf + SUBSTRING(@s, 128, 20) + case when len(@s) > 149 then @crlf + SUBSTRING(@s, 149, 20) else '' end;[/code]

RE: Print Text Month Calendar

Pete Cox — Fri, 30 Nov 2012 02:57:38 GMT

Hi Bernabe,Nice idea if you have the need.I was thinking of a less procedural approach and came up with this.Given a "Calendar Table" with the following definitionCREATE TABLE [dbo].[Dates]( [CalendarDate] [smalldatetime] NOT NULL, [isWeekday] [bit] NULL, [isHoliday] [bit] NULL, [CalendarYear] [smallint] NULL, [CalendarQuarter] [tinyint] NULL, [CalendarMonth] [tinyint] NULL, [FinancialYear] [smallint] NULL, [FinancialQuarter] [tinyint] NULL, [FinancialMonth] [tinyint] NULL, [DayOfMonth] [tinyint] NULL, [DayOfWeek] [tinyint] NULL, [MonthName] [varchar](9) NULL, [ShortMonthName] [char](3) NULL, [DayName] [varchar](9) NULL, [ShortDayName] [char](3) NULL, [SQL_Week] [tinyint] NULL, [Week] [tinyint] NULL, [HolidayDescription] [varchar](255) NULL) ON [PRIMARY]the following T-SQL seems to work quite wellDECLARE @supplieddate DATETIMEDECLARE @lastdayofmonth DATETIMEDECLARE @firstdayofmonth DATETIMEDECLARE @dayofweek INTDECLARE @calendarbody VARCHAR(MAX)DECLARE @title VARCHAR(MAX)DECLARE @monthname VARCHAR(12)DECLARE @paddedmonthname VARCHAR(24)DECLARE @newline CHAR SET @newline = CHAR(13)SET @supplieddate = DATEADD(MONTH,-0,CURRENT_TIMESTAMP)SELECT @dayofweek = DATEPART(dw,MIN(CalendarDate)), @firstdayofmonth = MIN(CalendarDate), @lastdayofmonth = MAX(CalendarDate), @monthname=DATENAME(MONTH,@supplieddate)FROM DatesWHERE CalendarMonth = MONTH(@supplieddate) AND CalendarYear = YEAR(@supplieddate)GROUP BY CalendarMonth, CalendarYear SET @paddedmonthname = REPLICATE(' ',6) + @monthname + ' ' + RTRIM(CAST(YEAR(@supplieddate) AS CHAR)) + REPLICATE(' ',6 + (LEN(@monthname) % 2)) SET @title = ' <<' + SUBSTRING(@paddedmonthname, (LEN(@paddedmonthname) / 2) - 4 + (LEN(@paddedmonthname) % 2), 16) + '>>' +@newline +' Su Mo Tu We Th Fr Sa';SET @calendarbody = REPLICATE(' ', 3 * (@dayofweek - 1))SELECT @calendarbody = @calendarbody + RIGHT(' ' + CAST(DayofMonth AS VARCHAR(5)),3) + (CASE WHEN DayOfWeek = 7 THEN @newline ELSE '' END) FROM Dates WHERE CalendarMonth = MONTH(@supplieddate) AND CalendarYear = YEAR(@supplieddate)PRINT @title + @newline + @calendarbodyI realise that I am using the quirky update feature, but thought it was OK for nowMy apologies for the poor formatting,Pete

Print Text Month Calendar

diaz.bernabe — Wed, 28 Nov 2012 20:34:31 GMT

Comments posted to this topic are about the item [B]Print Text Month Calendar[/B]