1.【問題現象】
在中文Windows環境的SQL*Plus中使用如下日期操作SQL時報錯。具體報錯資訊如下：
sec@ora10g> insert into t values (TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into t values (TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))
                               *
第 1 行出現錯誤:
ORA-01855: AM/A.M. or PM/P.M. required

sec@ora10g> select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual;
select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual
                *
第 1 行出現錯誤:
ORA-01855: AM/A.M. or PM/P.M. required

2.【問題原因】
在本地NLS_DATE_LANGUAGE引數指定的語言中沒有找到“AM”這樣的時間定義，也就是說在具體的國家語言下此類“AM”的定義是不相同的，比如，在中文“SIMPLIFIED CHINESE”中就應該指定為“上午/下午”，在美國的語言中就應該指定為“A.M. / P.M.”，在英國語言或西歐語言中就要指定為“AM/PM”。注意其中的區別。

這裡給出一種查詢NLS_DATE_LANGUAGE引數的方法：
sec@ora10g> col PARAMETER for a30
sec@ora10g> col VALUE for a30
sec@ora10g> 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               AL32UTF8
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         UTF8
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

已選擇19行。

3.【問題處理】方法很簡單，第一類處理方法就是嚴格按照具體國家語言的定義格式書寫特定的時間字串，另一類處理方法是修改NLS_DATE_LANGUAGE引數以便適應某一種日期字串的寫法。下面從這兩類處理思想出發，給出三種可行的處理方法。
1）第一種方法：將原字串中的“AM”修改為中國特色的的“上午”
sec@ora10g> select TO_Date( '22/10/2009 12:00:00 上午', 'DD/MM/YYYY HH:MI:SS AM') from dual;

TO_DATE('22/10
--------------
22-10月-09

sec@ora10g> alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';

會話已更改。

sec@ora10g> select TO_Date( '22/10/2009 12:00:00 上午', 'DD/MM/YYYY HH:MI:SS AM') from dual;

TO_DATE('22/10/200912:00
------------------------
10/22/2009 12:00:00 上午

2）第二種處理方法：直接在session中修改NLS_DATE_LANGUAGE引數，即刻生效。
（1）修改NLS_DATE_LANGUAGE為“AMERICAN”
sec@ora10g> alter session set NLS_DATE_LANGUAGE = 'AMERICAN';

會話已更改。

（2）再一次嘗試查詢，此時已經不再報錯
sec@ora10g> select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('10/
------------
22-OCT-09

（3）格式化一下日期格式，以便更加清晰的檢視結果。
sec@ora10g> alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';

會話已更改。

sec@ora10g> select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('10/22/200912:
----------------------
10/22/2009 12:00:00 AM

3）第三種方法：修改Windows登錄檔中的NLS_LANG引數，以便達到迂迴的修改NLS_DATE_FORMAT引數的目的
（1）進入Windows登錄檔方法
點選Windows作業系統的左下角的“開始”（“start”），然後點選“執行”（Run），最後輸入“regedit”回車後便可進入到登錄檔介面。

（2）在登錄檔中按照下面的過程導航，即可定位到NLS_LANG變數
“My Computer” --&gt “HKEY_LOCAL_MACHINE” --&gt “SOFTWARE” --&gt “ORACLE” --&gt “KEY-OraDb10g_home1”
此時在登錄檔的右側就能發現“NLS_LANG”的身影了

（3）雙擊“NLS_LANG”，把內容替換為“AMERICAN_AMERICA.ZHS16GBK”或“AMERICAN_CHINA.ZHS16GBK”即可（NLS_LANG的第一部分“語言”起作用）。

（4）驗證查詢
sec@ora10g> select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('10/
------------
22-OCT-09

sec@ora10g> alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';

Session altered.

sec@ora10g> select TO_Date( '10/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('10/22/200912:
----------------------
10/22/2009 12:00:00 AM

4.小結
Oracle的日期操作本身相對其他資料型別操作來說複雜很多，在日常的使用過程中要多加總結與記錄。
鑑於本文中提到的問題，在書寫指令碼的時候或使用工具生成指令碼的時候,一定要注意指令碼中日期類字串的書寫格式。以防因此導致資料無法錄入。

Good luck.

-- The End --

【問題處理】ORA-01855: AM/A.M. or PM/P.M. required問題排查與解析

相關文章