Geek asks and answers

How can I update the date and time?

I need to request an update for the date and time. I know how to update a date alone, but I am having problems adding time to a function. Now, in its current form, he is reading 4/20/2011 1:32:07 PM. I need a recv_date field to read 4/21/2011 7:00:00 AM.

My request so far:

UPDATE cxadmin.ro_hist
   SET recv_date = '4/21/2011'
 WHERE recv_serial_nbr = 'SABTSMSSD'
+3
source share
3 answers

SQL date formats are notorious, so you should use TO_DATE to ensure that the string representation of the date is converted to the Oracle DATE data type :

UPDATE cxadmin.ro_hist
   SET recv_date = TO_DATE('4/21/2011', 'MM/DD/YYYY')
 WHERE recv_serial_nbr = 'SABTSMSSD'

Your example does not include the temporary part:

UPDATE cxadmin.ro_hist
   SET recv_date = TO_DATE('4/21/2011 7:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
 WHERE recv_serial_nbr = 'SABTSMSSD'
+8
source

You tried?

update cxadmin.ro_hist
set recv_date = '4/21/2011 07:00:00 AM'
where recv_serial_nbr ='SABTSMSSD'
+1
source

to_timestamp ('4/21/2011 7:00:00 AM', 'MM/DD/YYYY HH: MI: SS AM') TO_DATE

-1

More articles:


All Articles