ORA-01861: literal does not match format string

Question

5.00/5 (1 vote)

See more:

HI ALL

I HAD A SELECT QUERY WHILE EXECUTING I AM GETTING THE ERROR AS

ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.

Here is my code kindly provide your suggestions.

SQL

select cust_folio_no,count(cust_folio_no),com_ref_no,customer_name,
 product_name from d_trxn_perm where
 LOAD_MMYY IN (to_char(add_months(SYSDATE,-1),'mmyyyy'),to_char(add_months(SYSDATE,-2),'mmyyyy'),
 to_char(add_months(SYSDATE,-3),'mmyyyy'))AND settlement_Date IN (to_char(add_months(SYSDATE,-1),'mmyyyy'),to_char(add_months(SYSDATE,-2),'mmyyyy'),
 to_char(add_months(SYSDATE,-3),'mmyyyy')) and rownum < 100000 and rbi_reject_code IN ('01','11','02','12','03','13','04','14','05','15') and
 client_recon_status_code in ('F','F1')
 group BY COM_REF_NO ,CUST_FOLIO_NO,CUSTOMER_NAME,product_name HAVING COUNT(cust_folio_no) = 3

Rgds
Jagadeshkumar

Posted 1-Jun-12 19:51pm

Kumar from madras

Updated 1-Jun-12 20:12pm

Mehdi Gholam

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pablo Aliskevicius · Accepted Answer · 2012-06-03T04:03:00

Without seeing your table definition, we can only guess...

SQL

settlement_Date IN 
(to_char(add_months(SYSDATE,-1),'mmyyyy'),to_char(add_months(SYSDATE,-2),'mmyyyy'), to_char(add_months(SYSDATE,-3),'mmyyyy'))

My guess is: settlement_Date is a date, which cannot be compared to a string of length six.

Try this:

SQL

to_char(settlement_Date,'mmyyyy')
-------
IN (to_char(add_months(SYSDATE,-1),'mmyyyy'),
    to_char(add_months(SYSDATE,-2),'mmyyyy'), 
    to_char(add_months(SYSDATE,-3),'mmyyyy'))

Hope this helps,
Pablo.