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

On a sidenote: `TO_CHAR(MyDate,'YYYY')` results in a string. `0018` on the other hand is a number. Don't mix the two. In your case there shouldn't be a problem with it, but in other situations there may. So: `where extract(year from mydate) = 18` or `where to_char(mydate, 'yyyy') = '0018'`. Anyway, you may want to compare with some date constant instead, so as to enable Oracle to use an index, e.g. `where mydate <= date '0018-12-31'`. — Thorsten Kettner, Oct 29 '18 at 11:47

score 3 · Answer 1 · answered Oct 29 '18 at 11:31

3

Why not simplify it?

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

answered Oct 29 '18 at 11:31

Ori Marko

56,308
23
131
233

Thorsten Kettner · Answer 2 · 2018-10-29T11:41:23.727

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.

edited Oct 29 '18 at 11:41

answered Oct 29 '18 at 11:33

Thorsten Kettner

89,309
7
49
73

score 1 · Answer 3 · answered Oct 29 '18 at 11:31

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...

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

3 Answers3