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; NOW PLUS_1_MO PLUS_3_MO PLUS_12_M PLUS_1_YE --------- --------- --------- --------- --------- 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';