1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01843: not a valid month

Discussion in 'SQL PL/SQL' started by Shekhar81, Aug 13, 2009.

  1. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hello

    A small issue here. Am getting this error when running a query on a table using a DATE field in the where clause. However this is happening on oracle database and not on another.

    ORA-01843: not a valid month

    The query is:

    Code (SQL):

    SELECT *
      FROM case_trials
     WHERE (date_of_trial BETWEEN '01/04/2008' AND '31/03/2009')
       AND (case_status = 'OPEN')
    TO_DATE functions round the statement resolves the error but i am just curious as to why i would get the ORA-01843: not a valid month error in one database but not in the other.

    Also i should mention that queries are run through Oracle Forms.

    Thanks in Advance!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    NEVER rely on default date formats. I expect the NLS_DATE_FORMAT in one database allows implicit conversion of your string where, in the other database, the format does not:

    Code (SQL):
    SQL> CREATE TABLE case_trials(
      2          trial_no NUMBER,
      3          CASE    varchar2(240),
      4          date_of_trial DATE,
      5          case_status varchar2(20)
      6  );

    TABLE created.

    SQL>
    SQL> ALTER SESSION SET nls_date_Format = 'DD/MM/RRRR';

    SESSION altered.

    SQL>
    SQL> INSERT ALL
      2  INTO case_trials
      3  VALUES(11230, 'Petty theft', '01/04/2008', 'CLOSED')
      4  INTO case_trials
      5  VALUES(11230, 'Petty theft', '08/04/2008', 'OPEN')
      6  INTO case_trials
      7  VALUES(11230, 'Petty theft', '16/05/2008', 'CLOSED')
      8  INTO case_trials
      9  VALUES(11230, 'Petty theft', '21/09/2008', 'OPEN')
     10  INTO case_trials
     11  VALUES(11230, 'Petty theft', '11/02/2009', 'OPEN')
     12  INTO case_trials
     13  VALUES(11230, 'Petty theft', '31/03/2009', 'CLOSED')
     14  INTO case_trials
     15  VALUES(11230, 'Petty theft', '01/04/2009', 'OPEN')
     16  INTO case_trials
     17  VALUES(11230, 'Petty theft', '21/06/2009', 'OPEN')
     18  INTO case_trials
     19  VALUES(11230, 'Petty theft', '07/11/2009', 'OPEN')
     20  SELECT * FROM dual;

    9 ROWS created.

    SQL>
    SQL> COLUMN CASE format a30
    SQL>
    SQL> SELECT *
      2    FROM case_trials
      3   WHERE (date_of_trial BETWEEN '01/04/2008' AND '31/03/2009')
      4     AND (case_status = 'OPEN');

      TRIAL_NO CASE                           DATE_OF_TR CASE_STATUS
    ---------- ------------------------------ ---------- --------------------
         11230 Petty theft                    08/04/2008 OPEN
         11230 Petty theft                    21/09/2008 OPEN
         11230 Petty theft                    11/02/2009 OPEN

    SQL>
    SQL> ALTER SESSION SET  nls_date_Format = 'MM/DD/RRRR';

    SESSION altered.

    SQL>
    SQL> SELECT *
      2    FROM case_trials
      3   WHERE (date_of_trial BETWEEN '01/04/2008' AND '31/03/2009')
      4     AND (case_status = 'OPEN');
     WHERE (date_of_trial BETWEEN '01/04/2008' AND '31/03/2009')
                                                   *
    ERROR at line 3:
    ORA-01843: NOT a valid MONTH


    SQL>