Warning _ORA-01861: literal does not match format string

[PRIYARAJ]

Hi

I am getting this warning in my server jobs.I have been running the jobs for about 2 weeks,but I dint get this warning anytime.It happened only when we did Incremental Load.Is there anything wrong with data & datatype.The source & target are both Oraoci stage reading from & writing to database.The warning I am getting is

TRGT_TABLE:ORA-01861: literal does not match format string & I get this for target table.

[kcbland]

The message is precise. You are passing in data that doesn't match a format string. This is common with DATE datatypes because generated SQL (really DML) includes TO_DATE functions on DATE and TIMESTAMP datatypes. Make sure you're passing dates formatted as strings in YYYY-MM-DD HH24:MI:SS format.

[Abburi]

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.

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 get Warning _ORA-01861: literal does not match format string

You could correct the SQL statement as follows:

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

Please check the data in the Source and format it accordingly.

[PRIYARAJ]

Thanks a lot for ur replies.I will trying changing the format.But ,This happens when I try to convert the Workdate .As my WorkDate is in format 01032008(DDMMYYYY).So when I try to do TO_DATE(#WorkDate#,'DD/MM/YYYY'),I am getting the warning.

Basically I have to do DELETE FROM TRGT_TABLE WHERE HLD_INSERT_DT <= (TO_DATE(#WorkDate#,'DD/MM/YYYY')-#ExpireDays#).

Where as HLD_INSERT_DT is in the format 3/1/2008 3:20:42 AM

Can you pls tell me if this is correct statement.?

Thanks in Advance!

[chulett]

A DATE isn't stored in any kind of external format, so your assertion regarding HLD_INSERT_DT is wrong. And the mask you use in a TO_DATE function should match what you have not what you think you want.

Code: Select all

TO_DATE(#WorkDate#,'DDMMYYYY')

Would be more appropriate.

[PRIYARAJ]

Thanks for your reply.

I tried SELECT TO_DATE('01032008','DD/MM/YYYY') FROM DUAL which is working fine.I think I had an error because I was not using ' ' when I was passing Workdate parameter.that is

SELECT TO_DATE(#WORKDATE#,'DD/MM/YYYY').


So am I correct that I should use the following:


SELECT TO_DATE('#WORKDATE#','DD/MM/YYYY').

wordate in quotes.

[chulett]

True, you need the single quotes around the job parameter. Missed that. But no, your date mask is still wrong, it needs to match whatever your job parameter looks like.

[PRIYARAJ]

True, you need the single quotes around the job parameter. Missed that. But no, your date mask is still wrong, it needs to match whatever your job parameter looks like. ...

Ok,So I will change the date mask also.Thank You so Much.

[ray.wurlod]

assertation

[chulett]