Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Literal does not match format string - 10g

    Hi guys

    I'm really confused here.

    I have to load a date into a column with data type 'date'.

    The date format is
    Code:
    01/04/2014 15:00
    And the formulas I've tried using are:

    Code:
    TO_DATE(DATETIME,'DD-MM-YYYY HH24:MI:SS')
    Code:
    TO_DATE(DATETIME,'DD/MM/YYYY HH24:MI:SS')
    Both work fine when checked by selecting from dual in Toad.......

    Code:
    select TO_DATE('01/04/2014  15:00:00','DD-MM-YYYY  HH24:MI:SS') RESULT from dual
    Code:
    RESULT
    01/04/2014 15:00:00
    But when I use this formula in my application, it says:

    Code:
    ORA-01861: Literal does not match format string
    NLS_DATE_FORMAT is

    Code:
    DD/MM/RR
    If someone could advise please?

    Regards
    Shajju

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does your application use colon characters for substitution within the SQL statement?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have to load a date into a column with data type 'date'.
    load from where exactly?

    >But when I use this formula in my application, it says:
    It is difficult to debug code that I can not see.
    Oracle reports error when something is wrong; but we can't know what you did wrong since you did not show us exactly what you did.
    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.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Yes it does use colons.

    Sorry, loading data from CSV files.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle is too dumb to lie.
    Believe what the error message says.
    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.

  6. #6
    Join Date
    Feb 2005
    Posts
    57
    CSV file: show us some example records
    SQL Loader? Show us your Control file
    SQL or PL/SQL? Show us your code statements

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    for a string of the format "01/04/2014 15:00" you need a format string of "MM/DD/YYYY HH24:MI" it must match exactly.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by beilstwh View Post
    for a string of the format "01/04/2014 15:00" you need a format string of "MM/DD/YYYY HH24:MI" it must match exactly.
    Should that not be dd/mm/yy hh24:mi (as per the OPs original statement of format.

Posting Permissions

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