Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Unanswered: ORA-01843: not a valid month - Not all as it should be?

    Morning folks,

    I have a strange one here.... I receive the following error message when attempting to run a query on a table using a Date field in the where clause:

    ORA-01843: not a valid month

    Now when I am running this query through Oracle forms, on one database, it returns the desired row of data, on another database, using the exact same Oracle Form, the operation generates the above error message.

    If I run the query in SQL Plus, it fails on both databases, which is even more bizarre!

    The query I am running is as follows:-


    SELECT *
    FROM CLAIMS
    WHERE (DATE_OF_LOSS BETWEEN '01/01/2000' AND '01/01/2003') and
    (CLAIM_STATUS = 'OPEN')

    Now I am aware that by using TO_DATE functions round the statement would cure the problem, however, I need to understand why on one database this query runs perfectly, yet on a different database, it fails. And as I said, why do they both fail in SQL PLus, but yet one of them works when running this through an oracle form?

    Any help would be EXTREMELY gratefully received.

    Thanks in Advance

  2. #2
    Join Date
    Apr 2006
    Posts
    5
    You could try checking the date format on each database to ensuer they are the same
    Code:
    select * from nls_session_parameters
    Hope that helps

  3. #3
    Join Date
    Apr 2006
    Posts
    3
    Wildy,

    Many thanks for your response; I tried this, and on both databases the NLS_DATE_FORMAT is set to 'DD-MON-RR', as I expected..... Any more suggestions?

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    Code:
    SQL> select * from nls_session_parameters;
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   ENGLISH
    NLS_TERRITORY                  UNITED KINGDOM
    NLS_CURRENCY                   
    NLS_ISO_CURRENCY               UNITED KINGDOM
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              ENGLISH
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH24.MI.SSXFF
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF
    NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR
    NLS_DUAL_CURRENCY              
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    
    17 rows selected.
    
    SQL> select sysdate from dual where sysdate between '01/01/2000' AND '11/04/2006';
    select sysdate from dual where sysdate between '01/01/2000' AND '11/04/2006'
                                                                    *
    ERROR at line 1:
    ORA-01843: not a valid month
    
    
    SQL> alter session set nls_date_format='dd/mm/rr';
    
    Session altered.
    
    SQL> select sysdate from dual where sysdate between '01/01/2000' AND '11/04/2006';
    
    SYSDATE
    --------
    10/04/06
    
    SQL>

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try changing the query to the following

    SELECT *
    FROM CLAIMS
    WHERE (DATE_OF_LOSS BETWEEN to_date('01/01/2000','mm/dd/yyyy') AND to_date('01/01/2003','mm/dd/yyyy')) and
    (CLAIM_STATUS = 'OPEN')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Apr 2006
    Posts
    3
    Thanks for the replies guys; I know both of those solutions work, but my problem is still why my query works on one of the databases I have, and yet on the other one, using the exact same form, it does not work.

    I have checked the NLS_DATE_FORMAT on both databases, and they are the same, and so I can only assume that the Oracle form is treating the data differently based on something in the database..... But what?

    Thanks again chaps!

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I suspect that your table contains null rows for DATE_OF_LOSS in CLAIMS for the server that is failing and doesn't for the one that is working. The not a valid month will occur when you do the implicent conversion to string when you use string variables to hold your date range.

    Run the following query on both servers

    select count(*) from claims where date_of_loss is null;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

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

    Cool


    NLS_DATE_FORMAT is client dependant -- Look at your 'client' environment variables.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    May 2007
    Posts
    5
    we are facing same issue, where as we are using Java application as client to insert records.

    it works fine if application connects to 10gR2 but if fails if application connects to 11gR2.

    any idea?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    different database & different NLS_DATE_FORMAT
    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.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    DO NOT RELY ON NLS_DATE_FORMAT in your code to format your date strings.
    As mentioned by others, this will cause issues with your code.

    Always code using to_date function when comparing dates. Otherwise, do not be surprised when your code breaks.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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