ORA-01821: date format not recognized

[Former Member]

Hi Experts,

I referred the below link to insert records into Oracle database.

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417700%29ID0040647550DB1250592038894850116...

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.

Thanks,

Vishal

Edited by: Vishal.P.Kulkarni on Nov 10, 2009 7:25 AM

[Former Member]

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

[Former Member]

I am getting 'format code appears twice' error now..

[Former Member]

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

[Former Member]

Issue solved by team-member.

currentDate: dd/MM/yyyy hh:mm:ss a

Constant:DD/MM/YYYY HH:MI:SS AM

Thanks,

Vishal