August 1, 2012 at 5:27 pm
Comments posted to this topic are about the item Print The Calender 2
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
August 1, 2012 at 7:38 pm
August 10, 2012 at 2:44 am
You are a bit US centric... 😉 Should have "SET DATEFIRST 7" in there if you want to make sure that your script runs correctly everywhere (or take @@DATEFIRST into account when identifiying the given day of week).
August 10, 2012 at 4:03 am
A calender is a series of hard pressure rollers used to form or smooth a sheet of material (see http://en.wikipedia.org/wiki/Calender).
Not sure how this relates to dates. :hehe:
August 10, 2012 at 1:32 pm
^^^^^
HA! That's exactly what I was thinking! CALENDAR <> CALENDER
(Maybe he drives around in a Camero, too...)
August 10, 2012 at 11:44 pm
Another way to print a calendar using PIVOT:
CREATE FUNCTION CalenderUsingPivot
(
@BeginDate date
, @EndDate date
)
RETURNS @Calendar TABLE
(
Week tinyint
, Monday tinyint
, Tuesday tinyint
, Wednesday tinyint
, Thursday tinyint
, Friday tinyint
, Saturday tinyint
, Sunday tinyint
)
AS
BEGIN
DECLARE @date TABLE
(
Date date
, Day AS DAY(Date)
, Week AS DATEPART(week, Date)
, Weekday AS DATENAME(weekday, Date)
);
DECLARE @i date;
SET @i = @BeginDate;
WHILE @i <= @EndDate
BEGIN
INSERT @date VALUES (@i);
SET @i = DATEADD(day, 1, @i);
END;
INSERT @Calendar
SELECT
Week
, p.Monday
, p.Tuesday
, p.Wednesday
, p.Thursday
, p.Friday
, p.Saturday
, p.Sunday
FROM
(
SELECT Day, Week, Weekday
FROM @date
) AS c
PIVOT
(
MAX(Day) FOR Weekday IN
(
Monday
, Tuesday
, Wednesday
, Thursday
, Friday
, Saturday
, Sunday
)
) AS p
ORDER BY Week;
RETURN;
END;
GO
SET LANGUAGE English;
SELECT *
FROM dbo.CalenderUsingPivot('2012-01-01', '2012-12-31');
August 13, 2012 at 5:29 am
I really liked your calendar idea, so I amended it a bit for my use..
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar2print]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].calendar2print
GO
/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 29 Jan 2008
EXEC calender '01/01/2016'
Purpose :- Print the calender from the given Date until end of current year
------------------------------------
Amended by Hannes Malan
Amended On :- Aug 13, 2012
EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1
Purpose :- Print the calender from the given Date ...
..and if @StartDate is null, use current date(@StartDate)
..also from specified date or from first day of specified month (@fullmonth)
..for a certain amount of months(@months)
****************************************************************************************/
Create Procedure calendar2print
@StartDate DateTime
,@fullmonth int = 1
,@months int = 1
As
set nocount on
if @StartDate is null set @StartDate = getdate()
if (@fullmonth = 1) begin
set @StartDate = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)
end
set datefirst 7
DECLARE @tbl Table(RowID int identity(1,1), Sun varchar(20) default '', Mon varchar(20) default '', Tue varchar(20) default '',
Wed varchar(20) default '', Thu varchar(20) default '', Fri varchar(20) default '', Sat varchar(20) default '')
declare @monthCount int
set @monthCount = 1
DECLARE @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)
set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Mon) values ('')
set @CurrentRowID = @@Identity
while @monthCount <= @months begin
set @monthCount = @monthCount + 1
set @TotalMonthDays = case when month(@StartDate) in (1,3,5,7,8,10,12) then 31
when month(@StartDate) in (4,6,9,11) then 30
when month(@StartDate) = 2 and year(@StartDate) % 4 = 0 then 29
when month(@StartDate) = 2 and year(@StartDate) % 4 <> 0 then 28
end
set @monthName = datename(m, @StartDate)
insert @tbl(Tue, Thu) values (@monthName, Convert(Varchar, year(@StartDate)))
set @CurrentRowID = @@Identity
insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat) values ('_______', '_______', '_______', '_______', '_______', '_______', '_______')
insert @tbl(Sun) values ('')
set @CurrentRowID = @@Identity
while @CurrentDate <= @TotalMonthDays begin
if @CurrentDay = 1 begin
insert @tbl(Sun) values ('')
set @CurrentRowID = @@Identity
end
if @CurrentDay = 1
update @tbl set Sun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 2
update @tbl set Mon = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 3
update @tbl set Tue = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 4
update @tbl set wed = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 5
update @tbl set Thu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 6
update @tbl set Fri = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 7
update @tbl set Sat = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
set @CurrentDate = @CurrentDate + 1
if @CurrentDate = @TotalMonthDays + 1 begin
IF (Datepart(DD,@StartDate)>1)
set @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate)
set @StartDate = DATEADD(Month,1,@StartDate)
set @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Sun,Mon,Tue,Wed,Thu,Fri,Sat)
values ('*******', '*******', '*******', '*******', '*******', '*******', '*******')
end else begin
set @CurrentDay = (@CurrentDay + 1) % 8
if (@CurrentDay = 0) set @CurrentDay = 1
end
end
set @CurrentDate = 1
end
-- Finaly show the output
select Sun, Mon, Tue, Wed, Thu, Fri, Sat
from @tbl
where Mon <> '' or Tue <> '' or Wed <> ''
or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''
August 13, 2012 at 5:44 am
hannes.malan (8/13/2012)
I really liked your calendar idea, so I amended it a bit for my use.
I have to ask... what will you actually end up using this for? Not challenging what you're doing. I just really want to know.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2012 at 5:59 am
hehehe.. , so far ...I have no idea...
I just like the odd script that is off the beaten track
😛
to expound a bit further...
...one might not use the script it was written for, but at times you get to a situation where you need similar logic to solve some kind of bussiness need.
August 13, 2012 at 12:49 pm
One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.
Otherwise the script looks like it works like a charm...
August 13, 2012 at 3:11 pm
timothy.shawley (8/13/2012)
One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.Otherwise the script looks like it works like a charm...
That's what I saw, but it can easily be corrected if you add two conditions to the CASE
set @TotalMonthDays = case when @currentMonth in (1,3,5,7,8,10,12) then 31
when @currentMonth in (4,6,9,11) then 30
when @currentMonth = 2 and @YearNo % 400 = 0 then 29
when @currentMonth = 2 and @YearNo % 100 = 0 then 28
when @currentMonth = 2 and @YearNo % 4 = 0 then 29
when @currentMonth = 2 and @YearNo % 4 > 0 then 28
August 14, 2012 at 5:40 am
Indeed.
You can eliminate 7 evaluations from the vast majority of leap year candidates by simplifying and reordering the predicates:
SELECT
CASE
WHEN @CurrentMonth in (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @CurrentMonth in (4, 6, 9, 11) THEN 30
-- only '@CurrentMonth = 2' left
WHEN @YearNo % 4 > 0 THEN 28 -- not divisible by 4 => not leap year
-- only years divisible by 4 left; century leap years must be divisible by 400
WHEN @YearNo % 100 > 0 THEN 29 -- not a century year => leap year
WHEN @YearNo % 400 = 0 THEN 29 -- is divisible by 400 => leap year
ELSE 28
END AS DaysInMonth;
Admitedly, in this application where we're evaluating DaysInMonth only once, the loss of code clarity doesn't justify the simplification. If you were applying this DaysInMonth function to a large volume of currentMonth values, however, it would be worth considering.
August 14, 2012 at 6:19 am
I personaly cringe when I see manual calculations involving date/time. It's way to easy to mess up and there are usualy good library functions for it.
In T-SQL would I for instance do something like this
SELECT
DATEPART(
dd,
DATEADD(
dd,
-1,
DATEADD(
mm,
1,
CONVERT(varchar(8), @Date, 20) + '01'
)
)
) AS [DaysInMonth]
to get the numer of days in the current month. That way are you safe with all leap years and don't have to worry about a thing.
August 14, 2012 at 2:24 pm
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
June 24, 2013 at 8:10 am
As I need to support French speaking persons, as I live/work in France, I modified for the date changes:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[calendar]
GO
/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 29 Jan 2008
Purpose :- Print the calendar from the given Date
Modified On :- July 20, 2012
Modified On :- June 24, 2013 - changed calendar representation for French
--- EXEC calendar '01/06/2013'
****************************************************************************************/
Create Procedure calendar
@StartDate DateTime='01/01/2012' ----Default Value
As
Set nocount on
DECLARE @tbl Table(RowID int identity(1,1), Dim varchar(20) default '', Lun varchar(20) default '', Mar varchar(20) default '',
Mer varchar(20) default '', Jeu varchar(20) default '', Ven varchar(20) default '', Sam varchar(20) default '')
DECLARE @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)
DECLARE @YearNo int
set @YearNo = datepart(yy, @StartDate)
set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Lun) values ('')
set @CurrentRowID = @@Identity
while @currentMonth <= 12
begin
set @monthName = datename(m, @StartDate) ---upper(datename(m, @StartDate))
insert @tbl(Mar, Mer, Jeu) values (@monthName, ' xxx ', Convert(Varchar, @YearNo))
set @CurrentRowID = @@Identity
insert @tbl(Lun,Mar,Mer,Jeu,Ven,Sam,Dim) values ('________', '________', '________', '________', '________', '________', '________')
insert @tbl(Lun) values ('')
set @CurrentRowID = @@Identity
set @TotalMonthDays = case when @currentMonth in (1,3,5,7,8,10,12) then 31
when @currentMonth in (4,6,9,11) then 30
when @currentMonth = 2 and @YearNo % 4 = 0 then 29
when @currentMonth = 2 and @YearNo % 4 <> 0 then 28
end
while @CurrentDate <= @TotalMonthDays
begin
if @CurrentDay = 1
begin
insert @tbl(Lun) values ('')
set @CurrentRowID = @@Identity
end
if @CurrentDay = 1
update @tbl set Lun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 2
update @tbl set Mar = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 3
update @tbl set Mer = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 4
update @tbl set Jeu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 5
update @tbl set Ven = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 6
update @tbl set Sam = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 7
update @tbl set Dim = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
set @CurrentDate = @CurrentDate + 1
if @CurrentDate = @TotalMonthDays + 1
Begin
SET @currentMonth = @currentMonth + 1
IF (Datepart(DD,@StartDate)>1)
SET @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate)
SET @StartDate = DATEADD(Month,1,@StartDate)
SET @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Lun,Mar,Mer,Jeu,Ven,Sam,Dim) values ('************', '************', '************', '************', '************', '************', '************')
end
ELSE
set @CurrentDay = (@CurrentDay + 1) % 8
if (@CurrentDay = 0)
SET @CurrentDay = 1
end
set @CurrentDate = 1
end
-- Finaly show the output
select Lun, Mar, Mer, Jeu, Ven, Sam, Dim from @tbl where Lun <> '' or Mar <> '' or Mer <> '' or
Jeu <> '' or Ven <> '' or Sam <> '' or Dim <> ''
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply