Oracle Date - How to add years to date

Question

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?

Cirone

Answer 1

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

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

Fawkes

Answer 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.

Tangential

Answer 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

Stomy

Answer 4

You can try this:

someDate + interval '4' year

INTERVAL

Pahang

Just a recommendation. Avoid using INTERVAL datatype. It is ANSI/ISO compliant. Therefore the statement you wrote will not work for 29th Feb. Generally it is up to you to grantee that the result will be correct. While add_months is Oracle proprietary and generates valid dates in for cases. – Site

@Site Sorry to raise this post from the grave, but I was looking for a way to do this without having it stick to the last day of the month when calculating birthdays because add_month() fails for 2/28. When the next year is a leap year add_months(to_date('02-28','MM-DD')) returns 02-29, which isn't correct. I found doing the following always returns the correct date: (to_date('02-28','MM-DD') - 1) + interval '1' year + 1 returns 02-28, even if the next year is a leap year, and if you put in 02-29, it will return 03-01 on non-leap years. – Berrie

@Berrie Sadly, though, select (to_date('01-31','mm-dd') -1 ) + interval '1' month +1 from dual; always blows up with ORA-01839: date not valid for month specified. – Study

But we are adding years, not months. – Berrie

Answer 5

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

select add_months(someDate, numberOfYears * 12) from dual

might do the trick

Ontologism

Works for leap years, too. – Destroyer

Answer 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

Superannuated

Answer 7

        SELECT TO_CHAR(SYSDATE,'YYYY')-2 ANO FROM DUAL

Molybdenous