Update in year without changing the month, day and time

Asked

Viewed 538 times

0

I am trying to make a script to change the year of a table record where the dta_shutdown field has the date in the format of 08/25/2091 13:06:00 in Oracle database.

I need to change the year 2091.

When I run the script below it changes the year but also changes the day, month and leaves without the time.

UPDATE tabela_aa SET dta_encerramento = TO_DATE('2018', 'YYYY') where dta_encerramento between '28-07-2017' and '29-08-2017';

I need to change record of several different days for the same year.

Do you have any way to make this not happen and manage to change only the year of the date that is in the table record?

sql oracle date sql-update

  • Maybe add / subtract the required months. For example: adding 1 year would be the same thing as somar 12 meses, thus would not affect the remainder of the.

  • Maybe these links can help: https://dba.stackexchange.com/questions/48575/update-year-alone-in-date-oracle-11g/48604 https://asktom.oracle.com/pls/apex/asktom.search?tagchanging-a-year-within-a-date

2 answers

0


Convert to char searching the day and month, then add the year and convert to date again:

TO_DATE(TO_CHAR(dta_encerramento ,'dd/mm') || '/2018','dd/mm/yyyy')

To include the time together, use this:

TO_DATE(TO_CHAR(dta_encerramento ,'ddmmhh24miss') || '2018','ddmmhh24missRRRR')

  • I tested with this format change the year, but delete the time of the field. I need to keep the date and time

  • TO_DATE(TO_CHAR(dta_closing ,'dd/mm hh24miss') || '/2018','dd/mm/yyyy hh24miss')

  • Include in the answer an option to do with the time together. The important thing is you understand what you are doing...

  • I managed to resolve using UPDATE table_aa SET dta_shutdown = dta_shutdown - INTERVAL '71' YEAR WHERE dta_shutdown between '01-01-2091' and '29-12-2091'; Thank you all

-1

  • This type of answer would look better if you were positioned in the appropriate place of comment.

Browser other questions tagged sql oracle date sql-update

You are not signed in. Login or sign up in order to post.