ORA-01843:使用Select语句时，月份无效

Question

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)

 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.

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';
                                                                                 *

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

Answer 1

正如你所意识到的，问题出在这一部分：

C.PURCHASE_DATE>'01/01/15'

但你的方法不太对...

这是我的错误，当我输入“02/10/16”作为输入时，它被转换成“02-10-16”格式。

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

ORA-01843: not a valid month

C.PURCHASE_DATE>'01-JAN-15'

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

或最好是4位数的年份：

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

对于固定值，还可以使用明确的ANSI日期文本：

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

另一个观察是，你正在寻找该日期午夜后的记录；你可能真的想把午夜发生的事情也包括在内：

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