Update only date in datetime field in Pl / SQL

Question

So, I need to update some dates in the Oracle database, this is a datetime field, but I want the date to be updated and leave the time as is ... There the query looks something like this:

update table SET field = to_date('07312010','MMDDYY'); 

But it cancels the hours, minutes and seconds from the field, I want to update the date, but I want the hour to remain the same, any thoughts?

Answer 1

You can use:

 UPDATE TABLE SET field = TO_DATE('07312010' || ' ' || TO_CHAR(field, 'HH24:MI:SS'), 'MMDDYY HH24:MI:SS'); 

Answer 2

In Oracle, space is a minor issue, I changed it a bit.

 /* Formatted on 4/26/2017 5:56:31 AM (QP5 v5.115.810.9015) */ UPDATE telco_attendee SET startdate = TO_DATE( ( TO_CHAR(startdate, 'DD/MM/YYYY') || TO_CHAR(starttime, 'HH24:MI:SS') ) ,'DD/MM/YYYYHH24:MI:SS')