ORA-01843: 無效的月份，執行sql語句更改為美國語言後仍然失敗的解決辦法

Endv發表於2014-11-24
SQL

 ORA-01843: 無效的月份
失敗的 sql 為:XXXXXXXXXXXXXXXX

執行sql語句更改為美國語言後仍然失敗，

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

 結果是登錄檔的問題，優化後多餘的語言被清理掉了，所以，要修復登錄檔

將下面的檔案複製儲存為登錄檔檔案.reg雙擊匯入登錄檔。

 windows server 2005 系統時區檔案 Time Zones.reg的檔案內容，適用於windows server 2008 r2

我是從2005中匯出來的，然後匯入2008 結果正常，希望對大家有用

註冊資訊存在系統的三個不同的地方，現整合為一個檔案，省掉一些麻煩。

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones]
"TzVersion"=dword:07d90100
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Central Standard Time]
"MUI_Display"="@tzres.dll,-160"
"MUI_Dlt"="@tzres.dll,-161"
"MUI_Std"="@tzres.dll,-162"
"Display"="(UTC-06:00)中部時間(美國和加拿大)"
"Dlt"="中部夏令時"
"Std"="中部標準時間"
"TZI"=hex:68,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Eastern Standard Time]
"MUI_Display"="@tzres.dll,-110"
"MUI_Dlt"="@tzres.dll,-111"
"MUI_Std"="@tzres.dll,-112"
"Display"="(UTC-05:00)東部時間(美國和加拿大)"
"Dlt"="東部夏令時"
"Std"="東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Mountain Standard Time]
"MUI_Display"="@tzres.dll,-190"
"MUI_Dlt"="@tzres.dll,-191"
"MUI_Std"="@tzres.dll,-192"
"Display"="(UTC-07:00)山地時間(美國和加拿大)"
"Dlt"="山地夏令時"
"Std"="山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00



[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Pacific Standard Time]
"MUI_Display"="@tzres.dll,-210"
"MUI_Dlt"="@tzres.dll,-211"
"MUI_Std"="@tzres.dll,-212"
"Display"="(UTC-08:00)太平洋時間(美國和加拿大)"
"Dlt"="太平洋夏令時"
"Std"="太平洋標準時間"
"TZI"=hex:e0,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00



[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\US Eastern Standard Time]
"MUI_Display"="@tzres.dll,-130"
"MUI_Dlt"="@tzres.dll,-131"
"MUI_Std"="@tzres.dll,-132"
"Display"="(UTC-05:00)印地安那州(東部)"
"Dlt"="美國東部夏令時"
"Std"="美國東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\US Mountain Standard Time]
"MUI_Display"="@tzres.dll,-200"
"MUI_Dlt"="@tzres.dll,-201"
"MUI_Std"="@tzres.dll,-202"
"Display"="(UTC-07:00)亞利桑那"
"Dlt"="美國山地夏令時"
"Std"="美國山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\UTC]
"MUI_Display"="@tzres.dll,-930"
"MUI_Dlt"="@tzres.dll,-931"
"MUI_Std"="@tzres.dll,-932"
"Display"="(UTC)協調世界時"
"Dlt"="協調世界時"
"Std"="協調世界時"
"TZI"=hex:00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\China Standard Time]
"MUI_Display"="@tzres.dll,-570"
"MUI_Dlt"="@tzres.dll,-571"
"MUI_Std"="@tzres.dll,-572"
"Display"="(UTC+08:00)北京，重慶，香港特別行政區，烏魯木齊"
"Dlt"="中國夏令時"
"Std"="中國標準時間"
"TZI"=hex:20,fe,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\China Standard Time]
"MUI_Display"="@tzres.dll,-570"
"MUI_Dlt"="@tzres.dll,-571"
"MUI_Std"="@tzres.dll,-572"
"Display"="(UTC+08:00)北京，重慶，香港特別行政區，烏魯木齊"
"Dlt"="中國夏令時"
"Std"="中國標準時間"
"TZI"=hex:20,fe,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Central Standard Time]
"MUI_Display"="@tzres.dll,-160"
"MUI_Dlt"="@tzres.dll,-161"
"MUI_Std"="@tzres.dll,-162"
"Display"="(UTC-06:00)中部時間(美國和加拿大)"
"Dlt"="中部夏令時"
"Std"="中部標準時間"
"TZI"=hex:68,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Eastern Standard Time]
"MUI_Display"="@tzres.dll,-110"
"MUI_Dlt"="@tzres.dll,-111"
"MUI_Std"="@tzres.dll,-112"
"Display"="(UTC-05:00)東部時間(美國和加拿大)"
"Dlt"="東部夏令時"
"Std"="東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Mountain Standard Time]
"MUI_Display"="@tzres.dll,-190"
"MUI_Dlt"="@tzres.dll,-191"
"MUI_Std"="@tzres.dll,-192"
"Display"="(UTC-07:00)山地時間(美國和加拿大)"
"Dlt"="山地夏令時"
"Std"="山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\Pacific Standard Time]
"MUI_Display"="@tzres.dll,-210"
"MUI_Dlt"="@tzres.dll,-211"
"MUI_Std"="@tzres.dll,-212"
"Display"="(UTC-08:00)太平洋時間(美國和加拿大)"
"Dlt"="太平洋夏令時"
"Std"="太平洋標準時間"
"TZI"=hex:e0,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\US Eastern Standard Time]
"MUI_Display"="@tzres.dll,-130"
"MUI_Dlt"="@tzres.dll,-131"
"MUI_Std"="@tzres.dll,-132"
"Display"="(UTC-05:00)印地安那州(東部)"
"Dlt"="美國東部夏令時"
"Std"="美國東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Time Zones\US Mountain Standard Time]
"MUI_Display"="@tzres.dll,-200"
"MUI_Dlt"="@tzres.dll,-201"
"MUI_Std"="@tzres.dll,-202"
"Display"="(UTC-07:00)亞利桑那"
"Dlt"="美國山地夏令時"
"Std"="美國山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\China Standard Time]
"MUI_Display"="@tzres.dll,-570"
"MUI_Dlt"="@tzres.dll,-571"
"MUI_Std"="@tzres.dll,-572"
"Display"="(UTC+08:00)北京，重慶，香港特別行政區，烏魯木齊"
"Dlt"="中國夏令時"
"Std"="中國標準時間"
"TZI"=hex:20,fe,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\Central Standard Time]
"MUI_Display"="@tzres.dll,-160"
"MUI_Dlt"="@tzres.dll,-161"
"MUI_Std"="@tzres.dll,-162"
"Display"="(UTC-06:00)中部時間(美國和加拿大)"
"Dlt"="中部夏令時"
"Std"="中部標準時間"
"TZI"=hex:68,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\Eastern Standard Time]
"MUI_Display"="@tzres.dll,-110"
"MUI_Dlt"="@tzres.dll,-111"
"MUI_Std"="@tzres.dll,-112"
"Display"="(UTC-05:00)東部時間(美國和加拿大)"
"Dlt"="東部夏令時"
"Std"="東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\Mountain Standard Time]
"MUI_Display"="@tzres.dll,-190"
"MUI_Dlt"="@tzres.dll,-191"
"MUI_Std"="@tzres.dll,-192"
"Display"="(UTC-07:00)山地時間(美國和加拿大)"
"Dlt"="山地夏令時"
"Std"="山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\Pacific Standard Time]
"MUI_Display"="@tzres.dll,-210"
"MUI_Dlt"="@tzres.dll,-211"
"MUI_Std"="@tzres.dll,-212"
"Display"="(UTC-08:00)太平洋時間(美國和加拿大)"
"Dlt"="太平洋夏令時"
"Std"="太平洋標準時間"
"TZI"=hex:e0,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,0b,00,00,00,01,00,02,00,00,\
  00,00,00,00,00,00,00,03,00,00,00,02,00,02,00,00,00,00,00,00,00



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\US Eastern Standard Time]
"MUI_Display"="@tzres.dll,-130"
"MUI_Dlt"="@tzres.dll,-131"
"MUI_Std"="@tzres.dll,-132"
"Display"="(UTC-05:00)印地安那州(東部)"
"Dlt"="美國東部夏令時"
"Std"="美國東部標準時間"
"TZI"=hex:2c,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\US Mountain Standard Time]
"MUI_Display"="@tzres.dll,-200"
"MUI_Dlt"="@tzres.dll,-201"
"MUI_Std"="@tzres.dll,-202"
"Display"="(UTC-07:00)亞利桑那"
"Dlt"="美國山地夏令時"
"Std"="美國山地標準時間"
"TZI"=hex:a4,01,00,00,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\
  00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00

 

 

客戶端系統裡環境
 select sysdate from dual;
返回的結果是2014/11/24 20:28:13

進入登錄檔

Regedit.exe-- windows server 2008

 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1/NLS_LANGUAGE

如果是預設安裝oracle的話，NLS_LANGUAGE
這一項的值應該是SIMPLIFIED CHINESE_CHINA.ZHS16GBK（屬於中文環境）
雙擊修改其值為：AMERICAN_ AMERICA.ZHS16GBK （英文環境）
重啟資料庫

SQL> select sysdate from dual;

SYSDATE
--------------
24-11月-14

SQL>
 

 

相關文章