Print The Calender 2

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Comments posted to this topic are about the item Print The Calender 2

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice thanks added it to my SANDBOX for future use

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Jack B.

    SSC Enthusiast

    Points: 109

    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).

  • Andrew Diniz

    SSCommitted

    Points: 1852

    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:

  • ACinAZ

    SSCommitted

    Points: 1849

    ^^^^^

    HA! That's exactly what I was thinking! CALENDAR <> CALENDER

    (Maybe he drives around in a Camero, too...)

  • Arjen Krap

    SSC-Addicted

    Points: 473

    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');

  • hannes.malan

    SSC Veteran

    Points: 264

    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 <> ''

  • Jeff Moden

    SSC Guru

    Points: 996622

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hannes.malan

    SSC Veteran

    Points: 264

    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.

  • timothy.shawley

    SSC Veteran

    Points: 228

    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...

  • Luis Cazares

    SSC Guru

    Points: 183633

    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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Andrew Diniz

    SSCommitted

    Points: 1852

    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.

  • Jack B.

    SSC Enthusiast

    Points: 109

    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.

  • rob.patterson-1085747

    Old Hand

    Points: 324

    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'

    SET nocount on

    IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar

    CREATE TABLE #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))

    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)

    VALUES ('','','','','','','','','','','',@thisyear,'','','','','','','','','','','')

    SELECT @cnt = 1

    WHILE @cnt < 12

    BEGIN

    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)

    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),'','',''

    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)

    VALUES ('Sun','Mon','Tue','Wed','Thu','Fri','Sat','' ,'Sun','Mon','Tue','Wed','Thu','Fri','Sat','','Sun','Mon','Tue','Wed','Thu','Fri','Sat')

    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)

    SELECT case 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'

    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)

    SELECT col7+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

    WHERE cid = (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)

    SELECT col7+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

    WHERE cid = (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)

    SELECT col7+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

    WHERE cid = (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)

    SELECT case 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

    WHERE cid = (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)

    SELECT case 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

    WHERE cid = (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)

    VALUES ('','','','','','','','','','','','','','','','','','','','','','','')

    SET @cnt = @cnt + 3

    END

    SELECT col1 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

    IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and left(name,len('#calendar')) = '#calendar') drop table #calendar

  • Duncan RAY-360676

    SSC Enthusiast

    Points: 116

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply