I want to just update the day part of a date for all the records in table. For example if the date is 16-AUG-18
. I want to just update the day that is 16
to 15
. There are data in that particular column like:
16-AUG-16
16-AUG-17
17-AUG-18
16-AUG-19
17-AUG-20
17-AUG-21
I want to update just the day and make it like:
15-AUG-16
15-AUG-17
15-AUG-18
15-AUG-19
15-AUG-20
15-AUG-21
I am using oracle 12C as database. And the dates are in format of DD-MON-YY
I had a quick play on a test table I created.
create table table_a
(col1 date);
insert into table_a
(col1)
values
(trunc(sysdate));
insert into table_a
(col1)
values
(trunc(sysdate + 10));
insert into table_a
(col1)
values
(trunc(sysdate - 10));
commit;
select * from table_a;
The select below shows how to set the day to the 20th of the month
select
col1 target_date,
col1 + (20 - to_number(to_char(col1, 'dd'))) changed_date,
20 - to_number(to_char(col1, 'dd')) factor_to_add
from table_a;
select * from table_a;
TARGET_DA CHANGED_D FACTOR_TO_ADD
--------- --------- -------------
07-FEB-19 20-FEB-19 13
17-FEB-19 20-FEB-19 3
28-JAN-19 20-JAN-19 -8
To employ this in an update statement use
update your_table
set your_column = col1 + (20 - to_number(to_char(col1, 'dd')));
And obviously - change the number 20 to whatever day of the month you are looking for.