ORA-01843: not a valid month

in Oracle Database; Hello A small issue here. Am getting this error when running a query on a table using a DATE field ...

+ Post Reply + Post New Topic
Results 1 to 2 of 2
  1. #1
    Shekhar81's Avatar
    Shekhar81 is offline Junior Member Shekhar81 is on a distinguished road
    Join Date
    24 Nov 2008
    Posts
    16
    Document Uploads
    0

    Helpful? Yes No

    ORA-01843: not a valid month

    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.    Club-Oracle Complementary E-Books and Magazines
    Get your free Complementary Copy of Oracle Magazine

    You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.

  3. #2
    zargon's Avatar
    zargon is offline Forum Guru zargon is on a distinguished road
    Join Date
    22 Oct 2008
    Location
    Here, or maybe there, or ...
    Posts
    506
    Document Uploads
    0

    Helpful? Yes No

    Re: ORA-01843: not a valid month

    Quote Originally Posted by Shekhar81 View Post
    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!
    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>

Similar Threads

  1. ORA-01843: not a valid month
    By StryderKC in forum SQL PL/SQL
    Replies: 2
    Last Post: 01-17-2010, 08:29 AM
  2. Check Constraint for valid Values
    By tastybrownies in forum SQL PL/SQL
    Replies: 1
    Last Post: 11-06-2009, 08:28 AM
  3. IMP-00010 not a valid export file, header failed verification
    By orafan in forum Security, Backup and Recovery
    Replies: 5
    Last Post: 03-14-2009, 12:50 PM
  4. Replies: 6
    Last Post: 02-10-2009, 07:53 AM
  5. Replies: 3
    Last Post: 10-20-2008, 01:49 PM