CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Question User
Question User

Reputation: 2293

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 |

Upvotes: 3

Views: 9127

Answers (3)

Pava dharini
Pava dharini

Reputation: 13

Update table_name set column_name=Add_months(sysdate,-(extract(year from sysdate)-2019)*12) where ...;

In place of 2019, you can give the year you need to update

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

You can use an interval to adjust the date with the values you have shown, but there are dates it will fail for; for example, if one of your rows has 2016-02-29 then trying to add an interval of two years will get error ORA-01839: date not valid for month specified as 2018-02-29 is not a valid date. Interval addition just adds the interval exactly as shown, it doesn't attempt to compensate for leap years, or (if you add months rather than years) the different numbers of days in each month. (This is mentioned in the sixth bullet point in the documentation about datetime/interval arithmetic).

It's safer to use the add_months() function, which doesn't error but does silently adjust the generated result, which you need to be aware of:

with table1 (day_date) as (
  select date '2016-02-28' from dual
  union all select date '2016-02-29' from dual
  union all select date '2016-03-01' from dual
)
select day_date, add_months(day_date, 24)
from table1;

DAY_DATE   ADD_MONTHS(DAY_DATE,24)
---------- -----------------------
2016-02-28 2018-02-28             
2016-02-29 2018-02-28             
2016-03-01 2018-03-01

For your update you would do the same thing:

UPDATE table1
SET day_date = add_months(day_date, 24)
where ...

Your where clause looks a bit odd though. You may only want to update specific value, but this will update two indeterminate rows from all of those in 20014, 2015 and 2016. You're applying your rownum filter without any ordering. If you do want to limit how many rows are updated, but don't want them to be random rows, you would need to identify and update the rows in two steps (e.g. with a correlated update that has a subquery).

Upvotes: 1

XING
XING

Reputation: 9886

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

Upvotes: 4

Related Questions