Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Unanswered: Working with dates

    How can I add 'x' years (or months or days) to a date? (format YYYYMMDD).

    Ex: 2007-04-27 + 3 years

    Thank you.

    Anna - Verona (Italy)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If the date is a DB2 DATE datatype, then you can just add the amount desired (MyDate + 2 years) or (MyDate + 30 DAYS) or (MyDate + 3 months)

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And if the date is still a string, just convert it to a date first:
    Code:
    DATE('2007-04-27') + 3 YEARS
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by stolze
    And if the date is still a string, just convert it to a date first:
    Code:
    DATE('2007-04-27') + 3 YEARS
    Could I also convert a number into a date?

    DATE(20070427)

    Thank you.

    Anna

  5. #5
    Join Date
    Jun 2006
    Posts
    471
    no input must be character or use char(xxxx) to convert to character and create correct input format with substring or any other function....
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Numbers can be converted to dates by calling the DATE function. But in that case, the number identifies the number of days to be added to 0001-01-01. So completely different semantics are applied.

    You could write your own function like this:
    Code:
    CREATE FUNCTION NumberToDate(n INTEGER)
       RETURNS DATE
       DETERMINISTIC
       NO EXTERNAL ACTION
       RETURN DATE('0001-01-01') +
          ((n / 10000) - 1) YEARS +
          ((MOD(n / 10000) / 100) - 1) MONTHS +
          ((MOD(n / 100) - 1) DAYS;
    Another alternative may be to convert the number to a string and the string to a date. I haven't tried that, though.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2007
    Posts
    1
    Hi Everybody,
    Can i get books on db2 procedures and functions?
    Which is the good book?
    Thanks in advance

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    There is one on SQL/PL written by Paul Yip et al.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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