|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 313,
Visits: 168
|
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 88,
Visits: 243
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65,
Visits: 443
|
|
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 ;
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 313,
Visits: 168
|
|
Bill, I like your version!
Thanks.
|
|
|
|