ORA-01843: Not a valid month, While using Select Statement

Question

I have three tables following is the table description of tables

SQL> DESC DRIVERS12


Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 D_ID                                      NOT NULL VARCHAR2(30)
 DNAME                                              VARCHAR2(30)
 AGE                                                VARCHAR2(30)
 CITY                                               VARCHAR2(30)

SQL> DESC CARS12
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 C_ID                                      NOT NULL VARCHAR2(30)
 COMAPNY                                            VARCHAR2(30)
 MODEL                                              VARCHAR2(30)
 PURCHASE_DATE                                      DATE

SQL> DESC RESERVES12
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 D_ID                                      NOT NULL VARCHAR2(30)
 C_ID                                      NOT NULL VARCHAR2(30)
 DAY                                                VARCHAR2(10)

I have taken data in each tables using following syntax:

 SQL> INSERT INTO DRIVERS12 VALUES(‘&D_ID’,’&DNAME’,’&AGE’,’&CITY’);
Enter value for d_id: D1
Enter value for dname: P.RAI
Enter value for age: 22
Enter value for city: BENARAS
old   2: VALUES('&D_ID','&DNAME','&AGE','&CITY')
new   2: VALUES('D1','P.RAI','22','BENARAS')

1 row created.

SQL> INSERT INTO CARS12
  2  VALUES('&C_ID','&COMPANY','&MODEL',TO_DATE('&PURCHASE_DATE','DD/MM/YY'));
Enter value for c_id: C1
Enter value for company: ROYAL ENFIELD
Enter value for model: BULLET350
Enter value for purchase_date: 02/10/16
old   2: VALUES('&C_ID','&COMPANY','&MODEL',TO_DATE('&PURCHASE_DATE','DD/MM/YY')
)
new   2: VALUES('C1','ROYAL ENFIELD','BULLET350',TO_DATE('02/10/16','DD/MM/YY'))


1 row created.

SQL> INSERT INTO RESERVES12
  2  VALUES('&D_DI','&C_ID','&DAY');
Enter value for d_di: D1
Enter value for c_id: C1
Enter value for day: MONDAY
old   2: VALUES('&D_DI','&C_ID','&DAY')
new   2: VALUES('D1','C1','MONDAY')

1 row created.

When I try to run the following queries

SQL> SELECT C.MODEL,C.COMAPNY,D.CITY
  2  FROM DRIVERS12 D, CARS12 C, RESERVES12 R
  3  WHERE R.D_ID=D.D_ID AND R.C_ID=C.C_ID AND D.DNAME='M.GUHA' AND C.PURCHASE_DATE>'01/01/15';
                                                                                 *

I get this error at line 3

ERROR at line 3:
ORA-01843: not a valid month

I am using Oracle 10g, Can anyone tell me where I am going wrong?

Answer 1

As you've realised the problem is with this part:

C.PURCHASE_DATE>'01/01/15'

but your fix isn't quite right...

It was my mistake, when I enter "02/10/16" as input it is getting converted into "02-OCT-16" format.

Your PURCHASE_DATE column is a DATE as it should be, and a date doesn't have an intrinsic human-readable format - Oracle stores it with its own internal representation. When you query it that is converted to a readable format by your application or client, usually using your session's NLS_DATE_FORMAT setting - in your case that must be DD-MON-RR from the output you got when you queried the table. You can display it in any format you want by calling TO_CHAR(), but should leave it as a date for all processing.

The issue is that you're comparing that date value to to a string. Oracle tries to implicitly convert that string to a date, and because the string doesn't match your NLS settings it can't, and throws that error - the same as if you did an explicit conversion with the same format model:

select to_date('01/01/2015', 'DD-MON-RR') from dual;

ORA-01843: not a valid month

In your answer you are still doing that:

C.PURCHASE_DATE>'01-JAN-15'

but now the string does match your default format model so the implicit conversion now works, as the equivalent of an explicit to_date('01-JAN-15', 'DD-MON-RR').

But you should not rely on implicit conversions or assume anything about NLS settings. The same query will error if run in a session with a different language (as it relies on JAN being an English month abbreviation) or a different NLS_DATE_FORMAT setting. As your column is a date you should explicitly compare it with a date too, using a safer format model (though this still allows for ambiguity between day and month numbers):

C.PURCHASE_DATE > TO_DATE('01/01/15', 'DD/MM/RR')

or preferably with 4-digit years:

C.PURCHASE_DATE > TO_DATE('01/01/2015', 'DD/MM/YYYY')

and with a fixed value you can also use an unambiguous ANSI date literal:

C.PURCHASE_DATE > DATE '2015-01-01'

One other observation is that you're looking for records after midnight on that date; you might really want to include anything that happened at exactly midnight too:

C.PURCHASE_DATE >= DATE '2015-01-01'

Answer 2

It was my mistake, when I enter "02/10/16" as input it is getting converted into "02-OCT-16" format. I get to know about this by using

SELECT * 

statement.

So,

 SQL> SELECT C.MODEL,C.COMAPNY,D.CITY
  2  FROM DRIVERS12 D, CARS12 C, RESERVES12 R
  3  WHERE R.D_ID=D.D_ID AND R.C_ID=C.C_ID AND D.DNAME='M.GUHA' AND C.PURCHASE_D
ATE>'01-JAN-15';

is working like a charm.

Answer 3

You are going wrong here

C.PURCHASE_DATE>2015;

2015 is not date format allowed by oracle , replace 2015 to DD/MM/YY instead 
for example 24/02/19