ORA-01821: date format not recognized


SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

ORA-01821: date format not recognized

Q:Hi Experts,

I referred the below link to insert records into Oracle database.
http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417700%29ID0040647550DB12505920388948501161End?blog=/pub/wlg/2002

But I am getting ‘date format not recognized’ error.

Along with inserting data I’ve to convert 24Hr time format to 12Hr format before inserting.

So I used yyyy/MM/dd K:mm:ss a in the constant part before the 2nd concat as explained in the link.

I tried yyyy/MM/dd HH:MI:SS AM also.

Please suggest how this error can be resolved.

Ans:
I am able to convert the 24 hr format to 12 hr format (with AM PM in the end) using the below snippet… Check the cases…

yyyy/MM/dd hh:mm:ss a

———–
I am getting ‘format code appears twice’ error now.
———–
In Audit Log:

INSERT INTO Table_Name (PERSON_ID, PUBLISHED_DATE, ID, FLAG) VALUES (902107, TO_DATE(11/09/2009 23:39:19,MM/dd/yyyy hh:mm:ss a), U420273, N)

Unable to execute statement for table or stored procedure. ‘Table_Name’ (Structure ‘StatementName1’) due to java.sql.SQLException: ORA-01810: format code appears twice
———–
Issue solved by team-member.
currentDate: dd/MM/yyyy hh:mm:ss a
Constant:DD/MM/YYYY HH:MI:SS AM.

SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

Share this:
Share this page via Email Share this page via Stumble Upon Share this page via Digg this Share this page via Facebook Share this page via Twitter
PDF24    Send article as PDF   
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *