ORA-01843: not a valid month

[Shekhar81]

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:

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!

[zargon]

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:

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:

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>