Search code examples
sqloraclejdbcoracle-sqldeveloper

How to update day part of date in datetime column


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


Solution

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