ORA-01843与NLS_DATE_FORMAT问题分析

在Oracle SQL的where子句中传入字符类型参数'19-11月-08'，使得可以直接和日期类型比较，或者转换一下同日期类型比较。
如果传入的字符格式和NSL_DATE_FORMAT一致，那么可以不转换直接用，否则会报ORA-01861错误。如果转换得不正确，则可能会报ORA-01843或其他错误。

例如：

SQL> select count(*) from dba_objects where created>to_date('2008-12-01');
select count(*) from dba_objects where created>to_date('2008-12-01')

 

                                                       *
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配

SQL> select count(*) from dba_objects where created>to_date('19-11月-08','mm-dd-
yyyy');
select count(*) from dba_objects where created>to_date('19-11月-08','mm-dd-yyyy'
)

 

                                                       *
第 1 行出现错误:
ORA-01843: 无效的月份

不能识别的字符串和格式转换不对的字符串测试时会出现这样的错误。


使用to_date转换的格式字符串主要为'DD-MON-RR'/'DD-MON-RRRR'或'YYYY-MM-DD'/'YY-MM-DD'。


如果能识别，正确的结果应是这样。

SQL> select count(*) from dba_objects where created>'19-11月-08';

  COUNT(*)
----------
      4199

这个格式和会话的NLS_DATE_FORMAT参数值相关。

SQL> r
  1* select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual

DF                   DL
-------------------- --------------------
DD-MON-RR            SIMPLIFIED CHINESE


我们在会话级别修改一下这个参数的值。

SQL> alter session set nls_date_format='YYYY-MM-DD';

会话已更改。

SQL> select count(*) from dba_objects where created>'19-11月-08';
select count(*) from dba_objects where created>'19-11月-08'
                                               *
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配

这样，原来正确的操作就不对了。使用符合NLS_DATE_FORMAT格式的字符串则可以了。

SQL> select count(*) from dba_objects where created>'2008-12-01';

  COUNT(*)
----------
      4199
     
这个参数和应用的环境也有关系，有的应用会自动修改这个参数值。因此，测试工作最好放到sqlplus中进行。

数据库级别上这个参数的值是固定不变的，如下所示：

SQL> select * from v$nls_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SIMPLIFIED CHINESE
NLS_TERRITORY                  CHINA
NLS_CURRENCY                   ￥
NLS_ISO_CURRENCY               CHINA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              SIMPLIFIED CHINESE
NLS_CHARACTERSET               ZHS16GBK
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              ￥
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

已选择19行。

 

NLS_DATE_LANGUAGE是"SIMPLIFIED CHINESE",即简体中文。所以月份值是中文如"11月"。