如何解決ORA-01843與NLS_DATE_FORMAT問題

更新時(shí)間：2012年11月20日 15:37:36 作者：

本文將詳細(xì)介紹如何解決ORA-01843與NLS_DATE_FORMAT問題,需要的朋友可以參考下

在Oracle SQL的where子句中傳入字符類型參數(shù)'19-11月-08'，使得可以直接和日期類型比較，或者轉(zhuǎn)換一下同日期類型比較。
如果傳入的字符格式和NSL_DATE_FORMAT一致，那么可以不轉(zhuǎn)換直接用，否則會報(bào)ORA-01861錯(cuò)誤。如果轉(zhuǎn)換得不正確，則可能會報(bào)ORA-01843或其他錯(cuò)誤。
例如：
Trc代碼
SQL>

復(fù)制代碼代碼如下:

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

第 1 行出現(xiàn)錯(cuò)誤:
ORA-01861: 文字與格式字符串不匹配
Trc代碼
SQL>

復(fù)制代碼代碼如下:

 
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 行出現(xiàn)錯(cuò)誤:
ORA-01843: 無效的月份
不能識別的字符串和格式轉(zhuǎn)換不對的字符串測試時(shí)會出現(xiàn)這樣的錯(cuò)誤。
使用to_date轉(zhuǎn)換的格式字符串主要為'DD-MON-RR'/'DD-MON-RRRR'或'YYYY-MM-DD'/'YY-MM-DD'。
如果能識別，正確的結(jié)果應(yīng)是這樣。
SQL>

復(fù)制代碼代碼如下:

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

COUNT(*)
----------
4199
這個(gè)格式和會話的NLS_DATE_FORMAT參數(shù)值相關(guān)。

復(fù)制代碼代碼如下:

 
select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual 

DF DL
-------------------- --------------------
DD-MON-RR SIMPLIFIED CHINESE
我們在會話級別修改一下這個(gè)參數(shù)的值。
SQL> alter session set nls_date_format='YYYY-MM-DD';
會話已更改。
SQL>

復(fù)制代碼代碼如下:

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

第 1 行出現(xiàn)錯(cuò)誤:
ORA-01861: 文字與格式字符串不匹配
這樣，原來正確的操作就不對了。使用符合NLS_DATE_FORMAT格式的字符串則可以了。
SQL>

復(fù)制代碼代碼如下:

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

這個(gè)參數(shù)和應(yīng)用的環(huán)境也有關(guān)系，有的應(yīng)用會自動修改這個(gè)參數(shù)值。因此，測試工作最好放到sqlplus中進(jìn)行。
數(shù)據(jù)庫級別上這個(gè)參數(shù)的值是固定不變的，如下所示：
Trc代碼
SQL>

復(fù)制代碼代碼如下:

 
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 