Warning _ORA-01861: literal does not match format string
Moderators: chulett, rschirm, roy
Warning _ORA-01861: literal does not match format string
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.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: Warning _ORA-01861: literal does not match format string
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.
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.
Regards,
Abburi
Abburi
Re: Warning _ORA-01861: literal does not match format string
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!
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!
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.
Would be more appropriate.
Code: Select all
TO_DATE(#WorkDate#,'DDMMYYYY')
Last edited by chulett on Mon Mar 03, 2008 3:18 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: