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.
---
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.
---
