Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: year/calendar funtion

    Is there a way for db2 to display a complete calendar of a year? for example if I want to see the calendar for 2007, can db2 display it?

    Thanks ... Ellis

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Not sure about displaying it using db2, but you can do it with the OS cal command. The following works on AIX:

    cal 2007

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sure Lenny will be able to produce a better solution once he solves all today's sudokus, but here's my take:
    Code:
    #cat cal.sql
    with caltable (d, c) as (
    select date('2009-11-01'), 1  from sysibm.sysdummy1 t1
    union all
    select caltable.d + 1 day, c+1
    from sysibm.sysdummy1 t2, caltable
    where  caltable.d + 1 day < '2009-12-01' and c < 1000
    )
    select d from caltable order by 1
    ;
    #db2 -tf cal.sql
    
    D
    ----------
    11/01/2009
    11/02/2009
    11/03/2009
    11/04/2009
    11/05/2009
    11/06/2009
    11/07/2009
    11/08/2009
    11/09/2009
    11/10/2009
    11/11/2009
    11/12/2009
    11/13/2009
    11/14/2009
    11/15/2009
    11/16/2009
    11/17/2009
    11/18/2009
    11/19/2009
    11/20/2009
    11/21/2009
    11/22/2009
    11/23/2009
    11/24/2009
    11/25/2009
    11/26/2009
    11/27/2009
    11/28/2009
    11/29/2009
    11/30/2009
    
      30 record(s) selected.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Too easy: 2009

    Code:
    with in_year(in_year) as
    (select 2009 from sysibm.sysdummy1)
    , in_days(in_days) as
    (select days(date(varchar(in_year + 1) || '-' || '01-01')) - days(date(varchar(in_year) || '-' || '01-01')) 
       from in_year 
    )
    ,
    calendar(cdate, dayofweek, mdays, K) as
    (select date(varchar(in_year) || '-' || '01-01') - 1 day, varchar('', 3), in_days, 0
       from in_days, in_year 
    union all
    select cdate + 1 day, 
    case  dayofweek(cdate + 1 day) 
     when 1 then 'Sun' 
     when 2 then 'Mon' 
     when 3 then 'Tue' 
     when 4 then 'Wed'
     when 5 then 'Thu' 
     when 6 then 'Fri' 
     when 7 then 'Sat' 
    end, mdays, K + 1
    from calendar
    where K + 1 <= mdays
    ) 
    select cdate "Calendar Date", dayofweek "Day Of Week" 
    from   calendar  
    where  dayofweek > ' '
    Lenny

    P.S.

    Calendar Date Day Of Week
    2009-01-01 Thu
    2009-01-02 Fri
    2009-01-03 Sat
    2009-01-04 Sun
    2009-01-05 Mon
    2009-01-06 Tue
    2009-01-07 Wed
    2009-01-08 Thu
    2009-01-09 Fri
    2009-01-10 Sat
    2009-01-11 Sun
    2009-01-12 Mon
    2009-01-13 Tue
    2009-01-14 Wed
    2009-01-15 Thu
    2009-01-16 Fri
    2009-01-17 Sat
    2009-01-18 Sun
    2009-01-19 Mon
    2009-01-20 Tue
    2009-01-21 Wed
    2009-01-22 Thu
    2009-01-23 Fri
    2009-01-24 Sat
    2009-01-25 Sun
    2009-01-26 Mon
    2009-01-27 Tue
    2009-01-28 Wed
    2009-01-29 Thu
    2009-01-30 Fri
    2009-01-31 Sat
    2009-02-01 Sun
    2009-02-02 Mon
    2009-02-03 Tue
    2009-02-04 Wed
    2009-02-05 Thu
    2009-02-06 Fri
    2009-02-07 Sat
    2009-02-08 Sun
    2009-02-09 Mon
    2009-02-10 Tue
    2009-02-11 Wed
    2009-02-12 Thu
    2009-02-13 Fri
    2009-02-14 Sat
    2009-02-15 Sun
    2009-02-16 Mon
    2009-02-17 Tue
    2009-02-18 Wed
    2009-02-19 Thu
    2009-02-20 Fri
    2009-02-21 Sat
    2009-02-22 Sun
    2009-02-23 Mon
    2009-02-24 Tue
    2009-02-25 Wed
    2009-02-26 Thu
    2009-02-27 Fri
    2009-02-28 Sat
    2009-03-01 Sun
    2009-03-02 Mon
    2009-03-03 Tue
    2009-03-04 Wed
    2009-03-05 Thu
    2009-03-06 Fri
    2009-03-07 Sat
    2009-03-08 Sun
    2009-03-09 Mon
    2009-03-10 Tue
    2009-03-11 Wed
    2009-03-12 Thu
    2009-03-13 Fri
    2009-03-14 Sat
    2009-03-15 Sun
    2009-03-16 Mon
    2009-03-17 Tue
    2009-03-18 Wed
    2009-03-19 Thu
    2009-03-20 Fri
    2009-03-21 Sat
    2009-03-22 Sun
    2009-03-23 Mon
    2009-03-24 Tue
    2009-03-25 Wed
    2009-03-26 Thu
    2009-03-27 Fri
    2009-03-28 Sat
    2009-03-29 Sun
    2009-03-30 Mon
    2009-03-31 Tue
    2009-04-01 Wed
    2009-04-02 Thu
    2009-04-03 Fri
    2009-04-04 Sat
    2009-04-05 Sun
    2009-04-06 Mon
    2009-04-07 Tue
    2009-04-08 Wed
    2009-04-09 Thu
    2009-04-10 Fri
    2009-04-11 Sat
    2009-04-12 Sun
    2009-04-13 Mon
    2009-04-14 Tue
    2009-04-15 Wed
    2009-04-16 Thu
    2009-04-17 Fri
    2009-04-18 Sat
    2009-04-19 Sun
    2009-04-20 Mon
    2009-04-21 Tue
    2009-04-22 Wed
    2009-04-23 Thu
    2009-04-24 Fri
    2009-04-25 Sat
    2009-04-26 Sun
    2009-04-27 Mon
    2009-04-28 Tue
    2009-04-29 Wed
    2009-04-30 Thu
    2009-05-01 Fri
    2009-05-02 Sat
    2009-05-03 Sun
    2009-05-04 Mon
    2009-05-05 Tue
    2009-05-06 Wed
    2009-05-07 Thu
    2009-05-08 Fri
    2009-05-09 Sat
    2009-05-10 Sun
    2009-05-11 Mon
    2009-05-12 Tue
    2009-05-13 Wed
    2009-05-14 Thu
    2009-05-15 Fri
    2009-05-16 Sat
    2009-05-17 Sun
    2009-05-18 Mon
    2009-05-19 Tue
    2009-05-20 Wed
    2009-05-21 Thu
    2009-05-22 Fri
    2009-05-23 Sat
    2009-05-24 Sun
    2009-05-25 Mon
    2009-05-26 Tue
    2009-05-27 Wed
    2009-05-28 Thu
    2009-05-29 Fri
    2009-05-30 Sat
    2009-05-31 Sun
    2009-06-01 Mon
    2009-06-02 Tue
    2009-06-03 Wed
    2009-06-04 Thu
    2009-06-05 Fri
    2009-06-06 Sat
    2009-06-07 Sun
    2009-06-08 Mon
    2009-06-09 Tue
    2009-06-10 Wed
    2009-06-11 Thu
    2009-06-12 Fri
    2009-06-13 Sat
    2009-06-14 Sun
    2009-06-15 Mon
    2009-06-16 Tue
    2009-06-17 Wed
    2009-06-18 Thu
    2009-06-19 Fri
    2009-06-20 Sat
    2009-06-21 Sun
    2009-06-22 Mon
    2009-06-23 Tue
    2009-06-24 Wed
    2009-06-25 Thu
    2009-06-26 Fri
    2009-06-27 Sat
    2009-06-28 Sun
    2009-06-29 Mon
    2009-06-30 Tue
    2009-07-01 Wed
    2009-07-02 Thu
    2009-07-03 Fri
    2009-07-04 Sat
    2009-07-05 Sun
    2009-07-06 Mon
    2009-07-07 Tue
    2009-07-08 Wed
    2009-07-09 Thu
    2009-07-10 Fri
    2009-07-11 Sat
    2009-07-12 Sun
    2009-07-13 Mon
    2009-07-14 Tue
    2009-07-15 Wed
    2009-07-16 Thu
    2009-07-17 Fri
    2009-07-18 Sat
    2009-07-19 Sun
    2009-07-20 Mon
    2009-07-21 Tue
    2009-07-22 Wed
    2009-07-23 Thu
    2009-07-24 Fri
    2009-07-25 Sat
    2009-07-26 Sun
    2009-07-27 Mon
    2009-07-28 Tue
    2009-07-29 Wed
    2009-07-30 Thu
    2009-07-31 Fri
    2009-08-01 Sat
    2009-08-02 Sun
    2009-08-03 Mon
    2009-08-04 Tue
    2009-08-05 Wed
    2009-08-06 Thu
    2009-08-07 Fri
    2009-08-08 Sat
    2009-08-09 Sun
    2009-08-10 Mon
    2009-08-11 Tue
    2009-08-12 Wed
    2009-08-13 Thu
    2009-08-14 Fri
    2009-08-15 Sat
    2009-08-16 Sun
    2009-08-17 Mon
    2009-08-18 Tue
    2009-08-19 Wed
    2009-08-20 Thu
    2009-08-21 Fri
    2009-08-22 Sat
    2009-08-23 Sun
    2009-08-24 Mon
    2009-08-25 Tue
    2009-08-26 Wed
    2009-08-27 Thu
    2009-08-28 Fri
    2009-08-29 Sat
    2009-08-30 Sun
    2009-08-31 Mon
    2009-09-01 Tue
    2009-09-02 Wed
    2009-09-03 Thu
    2009-09-04 Fri
    2009-09-05 Sat
    2009-09-06 Sun
    2009-09-07 Mon
    2009-09-08 Tue
    2009-09-09 Wed
    2009-09-10 Thu
    2009-09-11 Fri
    2009-09-12 Sat
    2009-09-13 Sun
    2009-09-14 Mon
    2009-09-15 Tue
    2009-09-16 Wed
    2009-09-17 Thu
    2009-09-18 Fri
    2009-09-19 Sat
    2009-09-20 Sun
    2009-09-21 Mon
    2009-09-22 Tue
    2009-09-23 Wed
    2009-09-24 Thu
    2009-09-25 Fri
    2009-09-26 Sat
    2009-09-27 Sun
    2009-09-28 Mon
    2009-09-29 Tue
    2009-09-30 Wed
    2009-10-01 Thu
    2009-10-02 Fri
    2009-10-03 Sat
    2009-10-04 Sun
    2009-10-05 Mon
    2009-10-06 Tue
    2009-10-07 Wed
    2009-10-08 Thu
    2009-10-09 Fri
    2009-10-10 Sat
    2009-10-11 Sun
    2009-10-12 Mon
    2009-10-13 Tue
    2009-10-14 Wed
    2009-10-15 Thu
    2009-10-16 Fri
    2009-10-17 Sat
    2009-10-18 Sun
    2009-10-19 Mon
    2009-10-20 Tue
    2009-10-21 Wed
    2009-10-22 Thu
    2009-10-23 Fri
    2009-10-24 Sat
    2009-10-25 Sun
    2009-10-26 Mon
    2009-10-27 Tue
    2009-10-28 Wed
    2009-10-29 Thu
    2009-10-30 Fri
    2009-10-31 Sat
    2009-11-01 Sun
    2009-11-02 Mon
    2009-11-03 Tue
    2009-11-04 Wed
    2009-11-05 Thu
    2009-11-06 Fri
    2009-11-07 Sat
    2009-11-08 Sun
    2009-11-09 Mon
    2009-11-10 Tue
    2009-11-11 Wed
    2009-11-12 Thu
    2009-11-13 Fri
    2009-11-14 Sat
    2009-11-15 Sun
    2009-11-16 Mon
    2009-11-17 Tue
    2009-11-18 Wed
    2009-11-19 Thu
    2009-11-20 Fri
    2009-11-21 Sat
    2009-11-22 Sun
    2009-11-23 Mon
    2009-11-24 Tue
    2009-11-25 Wed
    2009-11-26 Thu
    2009-11-27 Fri
    2009-11-28 Sat
    2009-11-29 Sun
    2009-11-30 Mon
    2009-12-01 Tue
    2009-12-02 Wed
    2009-12-03 Thu
    2009-12-04 Fri
    2009-12-05 Sat
    2009-12-06 Sun
    2009-12-07 Mon
    2009-12-08 Tue
    2009-12-09 Wed
    2009-12-10 Thu
    2009-12-11 Fri
    2009-12-12 Sat
    2009-12-13 Sun
    2009-12-14 Mon
    2009-12-15 Tue
    2009-12-16 Wed
    2009-12-17 Thu
    2009-12-18 Fri
    2009-12-19 Sat
    2009-12-20 Sun
    2009-12-21 Mon
    2009-12-22 Tue
    2009-12-23 Wed
    2009-12-24 Thu
    2009-12-25 Fri
    2009-12-26 Sat
    2009-12-27 Sun
    2009-12-28 Mon
    2009-12-29 Tue
    2009-12-30 Wed
    2009-12-31 Thu

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by n_i View Post
    I'm sure Lenny will be able to produce a better solution once he solves all today's sudokus, but here's my take:
    Code:
    #cat cal.sql
    with caltable (d, c) as (
    select date('2009-11-01'), 1  from sysibm.sysdummy1 t1
    union all
    select caltable.d + 1 day, c+1
    from sysibm.sysdummy1 t2, caltable
    where  caltable.d + 1 day < '2009-12-01' and c < 1000
    )
    select d from caltable order by 1
    ;
    #db2 -tf cal.sql
    
    D
    ----------
    11/01/2009
    11/02/2009
    11/03/2009
    11/04/2009
    11/05/2009
    11/06/2009
    11/07/2009
    11/08/2009
    11/09/2009
    11/10/2009
    11/11/2009
    11/12/2009
    11/13/2009
    11/14/2009
    11/15/2009
    11/16/2009
    11/17/2009
    11/18/2009
    11/19/2009
    11/20/2009
    11/21/2009
    11/22/2009
    11/23/2009
    11/24/2009
    11/25/2009
    11/26/2009
    11/27/2009
    11/28/2009
    11/29/2009
    11/30/2009
    
      30 record(s) selected.
    You forget, Kolja, about day of week, which is very important to any calendar, even russian....

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up ...and easier, with same result (not shown)

    Code:
    with in_year(in_year) as
    (select 2009 from sysibm.sysdummy1)
    ,
    calendar(cdate, dayofweek) as
    (select date(varchar(in_year) || '-' || '01-01') - 1 day, varchar('', 3)
       from in_year 
    union all
    select cdate + 1 day, 
    case  dayofweek(cdate + 1 day) 
     when 1 then 'Sun' 
     when 2 then 'Mon' 
     when 3 then 'Tue' 
     when 4 then 'Wed'
     when 5 then 'Thu' 
     when 6 then 'Fri' 
     when 7 then 'Sat' 
    end
    from calendar, in_year
    where year(cdate + 1 day) = in_year
    ) 
    select cdate "Calendar Date", dayofweek "Day Of Week" 
    from   calendar  
    where  dayofweek > ' '
    Lenny

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up ...and without CASE, with same result

    Code:
    with in_year(in_year) as
    (select 2009 from sysibm.sysdummy1)
    ,
    calendar(cdate, dayofweek) as
    (select date(varchar(in_year) || '-' || '01-01') - 1 day, varchar('', 3)
       from in_year 
    union all
    select cdate + 1 day, 
    substr('SunMonTueWedThuFriSat', 
                3 * (dayofweek(cdate + 1 day) - 1) + 1, 3)   
    from calendar, in_year
    where year(cdate + 1 day) = in_year
    ) 
    select cdate "Calendar Date", dayofweek "Day Of Week" 
    from   calendar  
    where  dayofweek > ' '
    Lenny

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Can you make it like a function? So, if I enter "db2cal 2007", it will display a calendar for 2007.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    UDF is possible

    Quote Originally Posted by db2girl View Post
    Can you make it like a function? So, if I enter "db2cal 2007", it will display a calendar for 2007.
    Bella, it has to be table UDF.
    I don't see any problem create this function.

    Lenny

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Lenny77 View Post
    Bella, it has to be table UDF.
    I don't see any problem create this function.

    Lenny

    Please show me an example of table UDF, if you have time tomorrow.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by db2girl View Post
    Please show me an example of table UDF, if you have time tomorrow.
    Ok, tomorrow.
    I am using few of them, which I created to make my life easier.

    One of them it was generator of integer numbers which is working very funny if you use random start and random step.

    Lenny

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I'm getting an error using Lenny' examples:

    SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by db2girl View Post
    I'm getting an error using Lenny' examples:

    SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884
    Which one ?
    Maybe you did not copy entire statement ?

    I never shown query before tested.

    Lenny

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Lenny77 View Post
    Which one ?
    Maybe you did not copy entire statement ?

    I never shown query before tested.

    Lenny

    Both of your examples don't work for me - I'm using db2 v9.5 on AIX.


    Maybe it's because you can provide integer to varchar() on z/OS but not on LUW.


    From z/OS manual:
    The VARCHAR function returns a varying-length character string representation of a character string, graphic string, datetime value, integer number, decimal number, floating-point number, or row ID value.


    From LUW manual:
    The VARCHAR function returns a varying-length character string representation of:
    A character string, if the first argument is any type of character string
    A graphic string (Unicode databases only), if the first argument is any type of graphic string
    A datetime value, if the argument is a date, time, or timestamp


    But I don't really know...

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Where are the pictures? What good is a calendar without pictures?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •