The error: ORA-01830: date format picture ends before converting entire input string..

 

 

 

Got error when they tried to insert data.

Insert into CAM_LOGS (COMMENTS,CREATED_BY,CREATED_TS) values ('INFO:[mess_wellcome_user] start sending email to : [e-mail] for user 44501','send_mail_users_AI',to_timestamp('08-MAR-11 02.27.35.000000000 PM EUROPE/LONDON','DD-MON-RR HH.MI.SSXFF AM'))
                                                                                                                                                                                                *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Insert into CAM_LOGS (COMMENTS,CREATED_BY,CREATED_TS) values ('INFO:[mess_wellcome_user] start sending email to : [e-mail] for user 44502','send_mail_users_AI',to_timestamp('08-MAR-11 02.27.35.000000000 PM EUROPE/LONDON','DD-MON-RR HH.MI.SSXFF AM'))
                                                                                                                                                                                               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string..

I found the data format is not correct on this:

Insert into CAM_LOGS (COMMENTS,CREATED_BY,CREATED_TS) values ('INFO:[mess_wellcome_user] start sending email to : [e-mail] for user 44501','send_mail_users_AI',to_timestamp('08-MAR-11 02.27.35.000000000 PM EUROPE/LONDON','DD-MON-RR HH.MI.SSXFF AM'))
                                                                                                                                                                                                *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Insert into CAM_LOGS (COMMENTS,CREATED_BY,CREATED_TS) values ('INFO:[mess_wellcome_user] start sending email to : [e-mail] for user 44502','send_mail_users_AI',to_timestamp('08-MAR-11 02.27.35.000000000 PM EUROPE/LONDON','DD-MON-RR HH.MI.SSXFF AM'))
                                                                                                                                                                                               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string..
Let us see the details about the fuction of TO_TIMESTAMP.

TO_TIMESTAMP:Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype

TO_TIMESTAMP_TZ:Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of the TIMESTAMP WITH TIME ZONE datatype

 

Then　Ｉthink we should use TO_TIMESTAMP_TZ becasue the data with the TIME ZONE.

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
11-MAR-11 12:22:03.425521 AM -07:00

SQL> select to_timestamp_tz('08-MAR-11 02.27.35.000000000 PM EUROPE/LONDON','DD-MON-RR HH.MI.SSXFF AM tzr') from dual;

TO_TIMESTAMP_TZ('08-MAR-1102.27.35.000000000PMEUROPE/LONDON','DD-MON-RRHH.M
---------------------------------------------------------------------------
08-MAR-11 02:27:35.000000000 PM EUROPE/LONDON

 

It works.