Question User Question User - 3 months ago 10
SQL Question

how to update the year field alone in oracle?

I have table like contain date field like this in oracle. I need to update the year alone in this date field. how can i do that ?

table1

DAY_DATE |
--------------------|
2014-07-31 16:00:00 |
2014-07-31 16:00:00 |


Tried Query

UPDATE table1
SET day_date = day_date+INTERVAL '2' YEAR(2014)
where extract(year from day_date) between 2014 and 2016 AND rownum < 3


Error


SQL Error [30088] [99999]: ORA-30088: datetime/interval precision is out of range

java.sql.SQLException: ORA-30088: datetime/interval precision is out of range


Expected Output

DAY_DATE |
--------------------|
2016-07-31 16:00:00 |
2016-07-31 16:00:00 |

Answer

This should work:

UPDATE table1
SET day_date  = day_date+INTERVAL '2' YEAR 

This will update next 2 years date. No need to add where clause

Comments