'Update statement to change timestamp field add year to current values [duplicate]

I need to be able to update a specific column in a table that is a time stamp. Its an expirationdate column and I need to add 5 years to the values in that. For these specific ids that are set to expire at some future date.Could anyone provide an example?

sqloracle-sqldeveloper


Solution 1:[1]

I presume that expiration date doesn't actually contain fractional seconds; that's most probably truncated to day, so date datatype is date. If that's so, then add_months is one option: 5 (years) * 12 (months) = 60 monts:

SQL> create table test as
  2    select sysdate as expiration_date from dual;

Table created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select expiration_date,
  2    add_months(expiration_date, 12 * 5) new_value
  3  from test;

EXPIRATION_DATE     NEW_VALUE
------------------- -------------------
06.04.2022 21:28:13 06.04.2027 21:28:13

SQL>

In order to update current values, you'd

update test set
  expiration_date = add_months(expiration_date, 12 * 5)
where ...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions