Home » SQL & PL/SQL » SQL & PL/SQL » add year to a date
add year to a date [message #11837] Mon, 19 April 2004 00:19 Go to next message
kim
Messages: 116
Registered: December 2001
Senior Member
hi,

I want a function, like add_months, so that I can increment a date  by a certain number of years.

eg. add_years(to_date('19.04.2004'),5) will return '19.05.2009'.

thanks for any help

 
Re: add year to a date [message #11838 is a reply to message #11837] Mon, 19 April 2004 00:52 Go to previous messageGo to next message
roblvl
Messages: 26
Registered: June 2003
Junior Member
TO ADD ONE YEAR
1* select SYSDATE + NUMTOYMINTERVAL(1,'YEAR') FROM DUAL
SQL> /

SYSDATE+N
---------
19-APR-05

TO ADD ONE YEAR AND 3 MONTHS

SQL> select sysdate+to_yminterval('01-03') from dual;

SYSDATE+T
---------
19-JUL-05
Re: add year to a date [message #11858 is a reply to message #11837] Tue, 20 April 2004 05:38 Go to previous messageGo to next message
jan
Messages: 71
Registered: August 2002
Member
try this

use add_months(date, 12* no. of years to add).

SELECT sysdate, add_months(sysdate,12*3) "3 Years"
FROM dual

hope this helps
JS
Re: add year to a date [message #11884 is a reply to message #11837] Tue, 20 April 2004 21:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
Although you have already been given two valid solutions, here is another one, just to give you exactly what you asked for, an add_years function:

scott@ORA92> CREATE OR REPLACE FUNCTION add_years
  2    (p_date	IN DATE,
  3  	p_years IN NUMBER)
  4    RETURN  DATE
  5  AS
  6  BEGIN
  7    RETURN ADD_MONTHS (p_date, p_years * 12);
  8  END add_years;
  9  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.


scott@ORA92> SELECT add_years(to_date('19.04.2004'),5) FROM DUAL
  2  /

ADD_YEARS(
----------
19.04.2009
Re: add year to a date [message #12051 is a reply to message #11858] Wed, 28 April 2004 02:55 Go to previous message
Pascal Rametse
Messages: 2
Registered: April 2004
Junior Member
This is too Good. It really works.
Previous Topic: Query to find two top salary person from table emp
Next Topic: Problems with Max
Goto Forum:
  


Current Time: Sat Oct 25 04:53:21 CDT 2014

Total time taken to generate the page: 0.09952 seconds