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>