Error while sending data to Oracle database

[Former Member]

Hi ALL,

i am getting this error while sending data to an Oracle database.

java.sql.SQLException: ORA-01861: literal does not match format string

i have used to_date function in my mapping and I dont see any issue with that. Can it be due to some fields exceeding the length assigned to them in the database?

Please help me to resolve this issue.

Thanks n Regards

Pushpinder Kaur

[Former Member]

I met the same problem.The solution is following:

/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database

Rewards points if it is helpful

[Former Member]

Hi Pushpinder,

Please paste the SQL statement that you have constructed.

Regards,

Shamly.

[aashish_sinha]

Hi,

You tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.

The options to resolve this Oracle error are:

Re-enter the literal so that it matches the format string.

For example, if you tried to execute the following statement:

SELECT to_date('20041308','yyyy/mm/dd')

FROM dual;

You will receive the error u getting.

You could correct the SQL statement as follows:

SELECT to_date('2004/08/13','yyyy/mm/dd')

FROM dual;

As a general rule, if you are using the to_date, to_timestamp, to_char, and similar functions, make sure that the literal that you provide matches the format string that you've specified.

hope this will help you.

Regards

Aashish Sinha

PS : reward points if helpful

[Former Member]

Hi,

I have given the literal and format of the same length. For eg.:

TO_DATE('2007-06-01 00:00:00',yyyy-mm-dd HH24:MI:SS')

still the problem persists.

Please help

Thanks n Regards

Pushpinder Kaur

[Former Member]

Hi Pushpinder,

TO_DATE('2007-06-01 00:00:00',yyyy-mm-dd HH24:MI:SS')

You have missed starting quotes for the 2nd argument in the above. Kindly insert as below.

TO_DATE('2007-06-01 00:00:00','yyyy-mm-dd HH24:MI:SS')

Regards,

Shamly.

[Former Member]

Sorry Shamly that was a typo. Its the same as you have suggested.

[Former Member]

Try giving capitals for YYYY-MM-DD

Regards,

Shamly.

[GabrielSagaya]

Remove 24.

TO_DATE('2007-06-01 00:00:00','yyyy-mm-dd HH:MI:SS')

http://www.techonthenet.com/oracle/errors/ora01861.php

http://www.techonthenet.com/oracle/functions/to_timestamp.php

[aashish_sinha]

Hi,

Try like this..

TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') and hope for the result .

Regards

Aashish Sinha

PS : reward points if helpful

[Former Member]

Hey Pushpinder,

Please try executing the query directly from database and see the result.If it doesn't work ask the db admin to check the NLS_DATE_FORMAT environment variable set in the database. This might be set wrongly.

Rewards point if find helpful

Thanks

Amit Gupta

[former_member192343]

hi

try to create working sql query directly in oracle first.

then try to map you mesage to form of this sql query.