Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    13

    Unanswered: ORA-01861: literal does not match format string;

    I've created a query where I'm parsing, linking a few table together, etc. One table has a date of service format like 11-MAY-04. The other table has a date of service format like 05112004. I need to link these particular two tables together by date of service. When I try this, I get the following error message:
    ORA-01861: literal does not match format string;
    What does this mean? Is it saying it can't match the dates? Here's the string of text that it gives me the error on. Actually it points to the first line.
    substr(record_image,INSTR(record_image,'^',1,24)+1 ,
    (INSTR(RECORD_IMAGE,'^',1,25) - INSTR(RECORD_IMAGE,'^',1,24) - 1)) =
    PB.enc_date (+) ORDER BY PT.LNAME, PT.FNAME;

    Thanks~

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    are they both DATE fields or are they both CHARACTER fields or what??

    the error is telling you that you are comparing a CHARACTER field to a DATE field
    (or anything along those lines)

    in essence, you need to make characters match or make dates match

    PHP Code:
    to_date(record_image,  'DD-MON-YY') = to_date(enc_date'ddmmyyyy'
    if first is character and next is a date then:
    PHP Code:
    substr(record_image,INSTR(record_image,'^',1,24)+,
    (
    INSTR(RECORD_IMAGE,'^',1,25) - INSTR(RECORD_IMAGE,'^',1,24) - 1)) =
    to_char(PB.enc_date'DD-MON-YY') (+) 
    Last edited by The_Duck; 07-28-04 at 12:14.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by sfoster
    I've created a query where I'm parsing, linking a few table together, etc. One table has a date of service format like 11-MAY-04. The other table has a date of service format like 05112004. I need to link these particular two tables together by date of service. When I try this, I get the following error message:
    ORA-01861: literal does not match format string;
    What does this mean? Is it saying it can't match the dates? Here's the string of text that it gives me the error on. Actually it points to the first line.
    substr(record_image,INSTR(record_image,'^',1,24)+1 ,
    (INSTR(RECORD_IMAGE,'^',1,25) - INSTR(RECORD_IMAGE,'^',1,24) - 1)) =
    PB.enc_date (+) ORDER BY PT.LNAME, PT.FNAME;

    Thanks~
    It looks like the enc_date is a date field. If that is true, then try the following

    to_date(substr(record_image,INSTR(record_image,'^' ,1,24)+1,
    (INSTR(RECORD_IMAGE,'^',1,25) - INSTR(RECORD_IMAGE,'^',1,24) - 1)),'MMDDYYYY') =
    trunc(PB.enc_date (+)) ORDER BY PT.LNAME, PT.FNAME;

    or

    substr(record_image,INSTR(record_image,'^',1,24)+1 ,
    (INSTR(RECORD_IMAGE,'^',1,25) - INSTR(RECORD_IMAGE,'^',1,24) - 1)) =
    to_char(PB.enc_date (+),'MMDDYYYY') ORDER BY PT.LNAME, PT.FNAME;
    Last edited by beilstwh; 07-28-04 at 12:50.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2004
    Posts
    13
    Thanks for your reply. PB.Enc_date is a date field. I am trying to match it with a field within the record_image field (take from another table). Record_image is Varchar2.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    this should work then:

    to_char(PB.enc_date, 'DD-MON-YY')
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are comparing a literal and a date field they much match. If the literal is of the form "MMDDYYYY", the to_char must use a format mask of 'MMDDYYYY' . Comparing the two strings

    '01252004' and '25-JAN-04' will not work.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •