Working with dates

annamaria · #1 (**permalink**) 04-27-07, 14:46

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)

ARWinner · #2 (**permalink**) 04-27-07, 15:03

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

stolze · #3 (**permalink**) 04-28-07, 14:20

And if the date is still a string, just convert it to a date first:

Code:

DATE('2007-04-27') + 3 YEARS

annamaria · #4 (**permalink**) 04-29-07, 06:28

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

guyprzytula · #5 (**permalink**) 04-29-07, 06:37

no input must be character or use char(xxxx) to convert to character and create correct input format with substring or any other function....

stolze · #6 (**permalink**) 04-29-07, 13:54

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.