Results 1 to 4 of 4

Thread: Date error

  1. #1
    Join Date
    Dec 2010
    Posts
    11

    Talking Unanswered: Date error

    select ENTRY_DATE from CIRC.SUBS_TAG where ENTRY_DATE <> '10/27/2010'

    I get "ORA-01843: not a valid month" error. ENTRY_DATE is date type. Why do i get this error. Please help me .Thank you.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by varunkumar View Post
    select ENTRY_DATE from CIRC.SUBS_TAG where ENTRY_DATE <> '10/27/2010'

    I get "ORA-01843: not a valid month" error. ENTRY_DATE is date type. Why do i get this error. Please help me .Thank you.
    Because the current setting of NLS_DATE_FORMAT parameter does not match the format of the string '10/27/2010', so the implicit conversion fails. The best fix is its explicit conversion to date data type with correct format:
    Code:
    where ENTRY_DATE <> to_date( '10/27/2010', 'mm/dd/yyyy' )
    (if it is supposed to be October, 27th, 2010)

  3. #3
    Join Date
    Dec 2010
    Posts
    11

    Talking Thank you

    Quote Originally Posted by flyboy View Post
    Because the current setting of NLS_DATE_FORMAT parameter does not match the format of the string '10/27/2010', so the implicit conversion fails. The best fix is its explicit conversion to date data type with correct format:
    Code:
    where ENTRY_DATE <> to_date( '10/27/2010', 'mm/dd/yyyy' )
    (if it is supposed to be October, 27th, 2010)
    Thank you it works.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Most probably you have a NLS setting that identifies the literal '10/27/2010' using the pattern dd/mm/yyyy and thus 27 is rejected as an invalid month.

    You should always use the to_date() function including the necessary conversion pattern to convert a string literal to a valid date.
    Code:
    select ENTRY_DATE 
    from CIRC.SUBS_TAG 
    where ENTRY_DATE <> to_date('10/27/2010', 'dd/mm/yyy')
    As a DATE column in Oracle also contains a time part you might actually want to use trunc(ENTRY_DATE) in order to "normalize" the time part for both values:

    Code:
    select ENTRY_DATE 
    from CIRC.SUBS_TAG 
    where trunc(ENTRY_DATE) <> to_date('10/27/2010', 'dd/mm/yyy')

Posting Permissions

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