error in updating a date

[lydiattc]

Hi,

Hope this is an easy one for most of you. I got the following error. Can you help me fix it?

SQL> UPDATE EU_Setpoints SET Last_Update ='1-Aug-2003 5:05:31 PM'
2 WHERE Tag_ID=1;

UPDATE EU_Setpoints SET Last_Update ='1-Aug-2003 5:05:31 PM'
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Thanks,
Lydia
(ps, the astris should be under the first single quote)

 

[carp]

Try
to_date('1-Aug-2003 5:05:31 PM','1-Aug-2003 5:05:31 PM') instead of just '1-Aug-2003 5:05:31 PM'

The default date format for Oracle is 'DD-MON-YY' and can be changed via the parameter NLS_DATE_FORMAT. If you want to use a different date format, you have to map the various date components for Oracle via the TO_DATE function.

 

[lydiattc]

Thanks for your reply. I tried to_date. Here's the error message:

SQL> UPDATE EU_Setpoints SET Last_Update =to_date('1-Aug-2003 5:05:31 PM','1-Aug-2003 5:05:31 PM')
2 WHERE Tag_ID=1;
UPDATE EU_Setpoints SET Last_Update =to_date('1-Aug-2003 5:05:31 PM','1-Aug-2003 5:05:31 PM')
*
ERROR at line 1:
ORA-01821: date format not recognized

 

[carp]

Nuts! My mistake - I pasted the wrong string in!

UPDATE EU_Setpoints SET Last_Update =to_date('1-Aug-2003 5:05:31 PM','DD-Mon-YYYY HH:MI:SS PM');

 

[lydiattc]

Thanks. I had realized it might be a typo. It's working now. Thanks a lot!