Thread: ORA-01843: not a valid month
-
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!
- 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.
-
Re: ORA-01843: not a valid month
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
-
IMP-00010 not a valid export file, header failed verification
By orafan in forum Security, Backup and RecoveryReplies: 5Last Post: 03-14-2009, 01:50 PM -
From Month,To Month prompt from a single attribute Month in database for OBI
By info.deepu@gmail.com in forum SQL PL/SQLReplies: 6Last Post: 02-10-2009, 08:53 AM -
Generate a Calendar for a particular month ( Puzzle )
By rajavu in forum GeneralReplies: 3Last Post: 10-20-2008, 02:49 PM


LinkBack URL
About LinkBacks
Reply With Quote



