Search the Site


bottom corner

Adding or subtracting months or years for Oracle dates

This note illustrate how we can add or subtract entire months or years from a given date without having to calculate the number of days to add or subtract, which may be complicated due to leap years, various months having different number of days, etc.

This page is filed under keyword(s): oracle.

I ran into the need to do this because one of my users performed a big data import, and it was not until he finished that he realized somewhere along the way when he was preparing the data, instead of "2009", some of the years came out to be "1909". To fix this in the database, I made use of Oracle's built-in numtoyminterval() function, which stands for "Number to Year/Month Interval". The syntax is as follows:

numtoyminterval(n, interval_name)

"n" is the quantity, and "interval_name" is either "year" or "month". The following example illustrates its basic usage.

select sysdate as now,
sysdate + numtoyminterval(1,'month') as plus_1_month,
sysdate + numtoyminterval(3,'month') as plus_3_months,
sysdate + numtoyminterval(12,'month') as plus_12_months,
sysdate + numtoyminterval(1,'year') as plus_1_year
from dual;

--------- --------- --------- --------- ---------
20-MAR-09 20-APR-09 20-JUN-09 20-MAR-10 20-MAR-10

Armed with this Oracle built-in function, I simply ran the following update statement to correct the bad data that my user had imported today.

update example_table
set date_goes_here = date_goes_here + numtoyminterval(1,'year')
where date_goes_here between '1-jan-1909' and '31-dec-1909'
and trunc(entry_date,'ddd') = trunc(sysdate,'ddd')
and entry_by = 'careless_user';

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 20 Mar 2009

bottom corner