Oracle Date - How to add years to date

问题:

I have a date field

DATE = 10/10/2010

sum = 4 (this are number of years by calculation)

is there a way to add four years to 10/10/2010 and make it 10/10/2014?

回答1:

Try adding months (12 * number of years) instead. Like this-

add_months(date'2010-10-10', 48)

回答2:

Use add_months

Example:

SELECT add_months( to_date('10-OCT-2010'), 48 ) FROM DUAL;

Warning
add_months, returns the last day of the resulting month if you input the last day of a month to begin with.

So add_months(to_date('28-feb-2011'),12) will return 29-feb-2012 as a result.

回答3:

I believe you could use the ADD_MONTHS() function. 4 years is 48 months, so:

add_months(DATE,48)

Here is some information on using the function:

http://www.techonthenet.com/oracle/functions/add_months.php

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1157035034361

回答4:

I am not sure, if I understood Your question correctly, but

select add_months(someDate, numberOfYears * 12) from dual

might do the trick

回答5:

You can try this:

someDate + interval '4' year

INTERVAL

回答6:

One more option apart from ADD_MONTHS

SELECT
      SYSDATE,
      SYSDATE
      + TO_YMINTERVAL ( '1-0' )
FROM
      DUAL;


SYSDATE   SYSDATE+TO_YMINTERVAL('1-0')
--------- ----------------------------
29-OCT-13 29-OCT-14                   
1 row selected.


SELECT
      SYSDATE,
      SYSDATE
      + TO_YMINTERVAL ( '2-0' )
FROM
      DUAL;


SYSDATE   SYSDATE+TO_YMINTERVAL('2-0')
--------- ----------------------------
29-OCT-13 29-OCT-15                   
1 row selected.

SELECT
      TO_DATE ( '29-FEB-2004',
              'DD-MON-YYYY' )
      + TO_YMINTERVAL ( '1-0' )
FROM
      DUAL

   *
Error at line 4

ORA-01839: date not valid for month specified

But the last one is illegal since there is no 29th day of February in 2005, hence it fails on leap year cases (Feb 29)

Read the documentation for the same