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 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 = 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
;
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy