Add 10 years to a date column for several rows in Oracle

Question

I tried to update a bad date (with the year 0018 instead of 2018) in some rows of an Oracle DB table. So I would like to add 10 years for doing that. The problem that the date is now still the same. For instance the date can be 10-JUL-0018 or 02-NOV-0018

I tried this but it's not working:

UPDATE MyTable SET MyDate= add_months(MyDate, 2000 * 12),'DD/MM/YYYY HH24:MI:SS'))
Where MyMainField_ID IN (
SELECT MyMainField_ID
FROM MyTable  
where TO_CHAR(MyDate,'YYYY') = 0018
)   

I obtain this error:

SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification 01747. 00000 - "invalid user.table.column, table.column, or column specification" Could you please help me with that ?

Thanks in advance

Sebastien

Answer 1

Why not simplify it?

UPDATE MyTable SET MyDate= add_months(MyDate, 2000 * 12)
where TO_CHAR(MyDate,'YYYY') = 0018

Answer 2

Remove ,'DD/MM/YYYY HH24:MI:SS')):

UPDATE MyTable SET MyDate= add_months(MyDate, 2000 * 12)
Where MyMainField_ID IN (
SELECT MyMainField_ID
FROM MyTable  
where TO_CHAR(MyDate,'YYYY') = 0018
)

Seems to have been remained from some change of your statement...

Answer 3

You are over-complicating things.

update mytable
set mydate = mydate + interval '2000' year(4)
where mydate < date '0100-01-01'; -- some threshold date for when to apply the update

This may make use of an index on mydate, if such exists.