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

最新推荐文章于 2023-02-02 17:56:12 发布

最新推荐文章于 2023-02-02 17:56:12 发布 · 234 阅读

本文介绍了如何解决在Oracle数据库中插入包含时区信息的时间戳数据时遇到的ORA-01830错误。通过调整TO_TIMESTAMP函数为TO_TIMESTAMP_TZ函数，并正确设置日期格式模板，成功解决了日期格式不匹配的问题。

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:

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.