Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    11

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

    Ok, First let me start my saying that I have already put the wheels in motion for to fix the SQL to a better standard. I still need to understand what changed.

    He is the query

    select * from myTable where My_Date = '2007-07-25 00:00:00.000'

    This has been in production for months and has returned rows.
    Last night it failed with "ORA-01861: literal does not match format string"

    When I run this SQL in my dev platform, it works. I know how to fix the sql so that it works in every environment. My question is what could cause SQL that has been in production for moths to suddenly fail?

    Please advise.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select * from myTable where My_Date = '2007-07-25 00:00:00.000'
    With Oracle characters between single quote marks are STRINGS; 'This may look like a date 2007-08-03 but is really a STRING'

    If/when you really want a DATE datatype use TO_DATE function.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Posts
    11
    Thank you. I know how the SQL sould be written. Now, please help to to understand why the SQL (Wrong) woks in dev, works in QA and has worked in prod till last night.

    I am looking to find the root cause of the change. I understand that it's bad SQL.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Most likely the nls_date_format is different between the two environments.
    Keep in mind that nls_date_format can be set at the session level too.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2005
    Posts
    11
    NLS_DATE_FORMAT DD-MON-RR

    They are the same in prod and Dev.

    How can I verify the value on a session level?

    Also, what would have changed this? It has always worked, then stopped. No change make to envrionment varables as far as I can tell.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I verify the value on a session level?
    Empirically.
    SELECT SYSDATE FROM DUAL;


    Also, what would have changed this?
    Human intervention is most likely the culprit.

    We have met the enemy & they is US.

    >select * from myTable where My_Date = '2007-07-25 00:00:00.000'
    >This has been in production for months and has returned rows.
    >NLS_DATE_FORMAT DD-MON-RR

    These statements are a blatant contradiction of each other!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    You may also want to check the NLS_DATE_FORMAT environment variable on your client/server where you are executing the SQL.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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