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 : ALEXANDRA.TORRES@ORACLE.COM 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 : MARCOS.FERREIRA@ORACLE.COM 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 : ALEXANDRA.TORRES@ORACLE.COM 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 : MARCOS.FERREIRA@ORACLE.COM 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.