November 28, 2012 at 8:34 pm
Comments posted to this topic are about the item Print Text Month Calendar
November 30, 2012 at 2:57 am
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 definition
CREATE 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 well
DECLARE @supplieddate DATETIME
DECLARE @lastdayofmonth DATETIME
DECLARE @firstdayofmonth DATETIME
DECLARE @dayofweek INT
DECLARE @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 Dates
WHERE 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 + @calendarbody
I realise that I am using the quirky update feature, but thought it was OK for now
My apologies for the poor formatting,
Pete
December 28, 2012 at 9:28 am
Here is a maintainable version...
DECLARE
@dt datetime,
@DayOneDayOfWeek int,
@DaysInMonth int,
@s-2 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 day
SET @DaysInMonth = (select datepart(day, dateadd(day, -1, dateadd(month, 1, cast(convert(varchar(8), @dt, 121)+'01' as datetime)))));
-- need to know day one weekday
SET @DayOneDayOfWeek = (select DatePart(WeekDay, cast(convert(varchar(8), @dt, 121)+'01' as datetime)));
-- build day numbers string and pad for first day
SET @s-2 = 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.
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
;
January 9, 2013 at 12:15 pm
Bill, I like your version!
Thanks.
May 5, 2016 at 7:27 am
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply