3

I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the recv_date field to read 4/21/2011 7:00:00 AM.

My query so far is:

UPDATE cxadmin.ro_hist
   SET recv_date = '4/21/2011'
 WHERE recv_serial_nbr = 'SABTSMSSD'
APC
  • 144,005
  • 19
  • 170
  • 281
Justin
  • 245
  • 5
  • 8
  • 12
  • 1
    This would require built-in functions to handle and maipulate date times which isnt a SQL standard and differs across implementation. Please indicate the database type(s) you are using or intend to target. – d-live May 10 '11 at 01:08

3 Answers3

7

SQL date formats are notoriously picky, requiring you to use TO_DATE to ensure that a string representation of a date is converted to an 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 doesn't include the time portion:

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'
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
1

Have you tried?

update cxadmin.ro_hist
set recv_date = '4/21/2011 07:00:00 AM'
where recv_serial_nbr ='SABTSMSSD'
Jeff Swensen
  • 3,513
  • 28
  • 52
  • wHEN I TRY this way I receive the DB error: ORA-01830: date format picture ends before converting entire input string – Justin May 10 '11 at 03:45
  • 3
    @Justin: that's because your default date format doesn't include the time portion. It's safer to use TO_DATE as OMG Ponies has suggested. – Jeffrey Kemp May 10 '11 at 05:45
-1

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

Evgen
  • 11