记录下ORACLE日期的运行

Connected to Personal Oracle9i Release 9.2.0.1.0
Connected as system


下面直接使用日期查询,会提示无效的月份错误:

引用

SQL> select * from books where pubdate ='21-01-01';

select * from books where pubdate ='21-01-01'

ORA-01843: 无效的月份

下面使用日期格式化函数,可以通过.

引用

SQL> select * from books where pubdate =to_date('21-01-01','dd-mm-yy');

ISBN       TITLE                          PUBDATE     PUBID    COST  RETAIL CATEGORY
---------- ------------------------------ ----------- ----- ------- ------- ------------
1059831198 BODYBUILD IN 10 MINUTES A DAY  2001-1-21       4   18.75   30.95 FITNESS
1915762492 HANDCRANKED COMPUTERS          2001-1-21       3   21.80   25.00 COMPUTER

这个没什么好说的.

引用

SQL> select * from books where pubdate =to_date('21-JAN-01','dd-mm-yy');

select * from books where pubdate =to_date('21-JAN-01','dd-mm-yy')

ORA-01858: 在要求输入数字处找到非数字字符

这个不能通过,即使使用了格式化函数.

引用

SQL> select * from books where pubdate =to_date('21-JAN-01','dd-mon-yy');

select * from books where pubdate =to_date('21-JAN-01','dd-mon-yy')

ORA-01843: 无效的月份

SQL> alter session set nls_date_language=English
  2  ;


更改了会话参数,后,可以使用了

引用

Session altered

SQL> select * from books where pubdate =to_date('21-JAN-01','dd-mon-yy');

ISBN       TITLE                          PUBDATE     PUBID    COST  RETAIL CATEGORY
---------- ------------------------------ ----------- ----- ------- ------- ------------
1059831198 BODYBUILD IN 10 MINUTES A DAY  2001-1-21       4   18.75   30.95 FITNESS
1915762492 HANDCRANKED COMPUTERS          2001-1-21       3   21.80   25.00 COMPUTER

SQL>