OraFAQ Forum: SQL & PL/SQL » add year to a date

Home » SQL & PL/SQL » SQL & PL/SQL » add year to a date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

add year to a date [message #11837]

Mon, 19 April 2004 00:19

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

�

Report message to a moderator

Re: add year to a date [message #11838 is a reply to message #11837]

Mon, 19 April 2004 00:52

roblvl
Messages: 27
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

Report message to a moderator

Re: add year to a date [message #11858 is a reply to message #11837]

Tue, 20 April 2004 05:38

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

Report message to a moderator

Re: add year to a date [message #11884 is a reply to message #11837]

Tue, 20 April 2004 21:08

Barbara Boehmer
Messages: 9105
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

Report message to a moderator

Re: add year to a date [message #12051 is a reply to message #11858]

Wed, 28 April 2004 02:55

Pascal Rametse
Messages: 2
Registered: April 2004

Junior Member

This is too Good. It really works.

Report message to a moderator

Previous Topic:	Query to find two top salary person from table emp
Next Topic:	Problems with Max

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 02:10:32 CDT 2025