Results 1 to 8 of 8

Thread: Date counting

  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Unanswered: Date counting

    Is there a way in SQL for IBM Ozs to count days between two dates according to a specific calendar (for example, Italian banking holidays calendar)?
    If there isn't, how could I count days between two dates without counting Saturdays and Sundays? In Excel there's the command DAY.WEEK with which week days are numbered from 1 (Monday) to 7 (Sunday) and I can instruct: "if weekday is <= 4 then weekday + 1 else weekday + 3. Of course Excel has a calendar beneath and knows that, say, 20-02-2008 is Wednesday (day 3 of the week).

    Thank you

    Anna - Verona (Italy)


  2. #2
    Join Date
    Jun 2006
    Posts
    471
    you could use the dayofweek(x) function
    date + 1 days can be used
    see sql reference how to calculate with dates
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may want to have a look at the TIMESTAMPDIFF function: http://publib.boulder.ibm.com/infoce...estampdiff.htm

    It can be used to calculate the amount of days between two timestamps. Note that it assumes that each month has 30 days, which is not really very exact - but that's in the nature of this function's input.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    Date counting in DB2 for z/OS

    Quote Originally Posted by annamaria
    Is there a way in SQL for IBM z/OS to count days between two dates according to a specific calendar (for example, Italian banking holidays calendar)?
    You'll indeed have to implement that function (UDF) yourself: there's no standard "NoOfWeekDays" function or so. Let alone one that knows about Italian banking holidays.

    Ingredients which will have to be used are:

    - the DAYS function. argument: an expression of datatype DATE; returns: an integer
    The difference between two DAYS return values is the nr. of days "between" the two, i.e., it will be 0 for equal dates, 1 or -1 for two consecutive days, etc.

    - the DAYOFWEEK function: argument: a DATE; returns: 1 for Sunday, 2 for Monday, ... 7 for Saturday.

    - a (small) table summing up the Italian banking holidays. Single column, datatype DATE.

    Around this, you may write a relatively simple UDF which returns the number of working days between two given dates.
    Alternatively, also a single SELECT statement could do this; in that case it will need to be a recursive one, still using the three mentioned ingredients.
    (Its implementation is left as an exercise... ;-)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by Peter.Vanroose
    You'll indeed have to implement that function (UDF) yourself: there's no standard "NoOfWeekDays" function or so. Let alone one that knows about Italian banking holidays.

    Ingredients which will have to be used are:

    - the DAYS function. argument: an expression of datatype DATE; returns: an integer
    The difference between two DAYS return values is the nr. of days "between" the two, i.e., it will be 0 for equal dates, 1 or -1 for two consecutive days, etc.

    - the DAYOFWEEK function: argument: a DATE; returns: 1 for Sunday, 2 for Monday, ... 7 for Saturday.

    - a (small) table summing up the Italian banking holidays. Single column, datatype DATE.

    Around this, you may write a relatively simple UDF which returns the number of working days between two given dates.
    Alternatively, also a single SELECT statement could do this; in that case it will need to be a recursive one, still using the three mentioned ingredients.
    (Its implementation is left as an exercise... ;-)

    How do I create a table?
    I mean I can set a list of the Italian banking holidays but how can I make these dates become records of a SQL table?
    Select '2008-12-25','2008-12-26' as COLUMN 1? I don't think this is exact.
    Thank you
    Anna - Verona (Italy)

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    How do I create a table?
    Code:
    CREATE TABLE banking_holidays (d DATE NOT NULL) ;
    INSERT INTO banking_holidays (d) VALUES ('2008-12-25') ;
    INSERT INTO banking_holidays (d) VALUES ('2008-12-26') ;
    ...
    The table name and the column name (d) may be chosen, of course.
    With this syntax, the table will be created in the default database; it is possible to an an "IN ..." at the end. See the SQL Reference Guide of your version of DB2 for the details.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE TABLE banking_holidays (d DATE NOT NULL) ;
    INSERT INTO banking_holidays (d) VALUES ('2008-12-25') ;
    INSERT INTO banking_holidays (d) VALUES ('2008-12-26') ;
    ...
    You can construct multiple rows by VALUES syntax on DB2 for LUW(at lest V5.2 or higher).
    Code:
    INSERT INTO banking_holidays (d) VALUES
     ('2008-12-25')
    ,('2008-12-26')
    ,('2008-12-31')
    ...;
    Unfortunately, the syntax is not supported on DB2 for z/OS.
    Last edited by tonkuma; 03-01-08 at 16:04.

  8. #8
    Join Date
    Apr 2007
    Posts
    51
    made in error

Posting Permissions

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