日期格式不一致是跨平台遷移、異構整合、應用報錯的高頻元兇。Oracle 提供 Session 級 (`ALTER SESSION`)、Instance 級 (初始化參數)、Client 端 (註冊表/環境變數) 三層面設定。本文系統梳理 `TO_DATE`/`TO_CHAR` 格式元素、`NLS_DATE_FORMAT` 修改層級、`NLS_TERRITORY` 關聯影響、Windows 註冊表永久生效路徑，並給出最佳實務建議。
核心格式化函數
| 函數 | 方向 | 範例 | 用途 |
|---|
| TO_DATE(str, fmt) | 字串 → DATE | TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') | 插入/比較日期常數 |
| TO_CHAR(date, fmt) | DATE → 字串 | TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') | 顯示/匯出/JSON 序列化 |
常用格式元素速查
| 元素 | 含義 | 範例輸出 |
|---|
| YYYY | 4 位元年 | 2024 |
| MM | 2 位元月 (01-12) | 01 |
| MON / MONTH | 月份縮寫/全名 | JAN / JANUARY |
| DD | 月內第幾天 | 15 |
| HH24 | 24 小時制 (00-23) | 14 |
| HH / HH12 | 12 小時制 (01-12) | 02 |
| MI | 分鐘 (00-59) | 30 |
| SS | 秒 (00-59) | 00 |
| FF / FF3 / FF6 | 小數秒 (精度 1-9 位) | .123456 |
| TZH:TZM | 時區位移 | +08:00 |
三層 NLS_DATE_FORMAT 設定層級
| 層級 | 指令/位置 | 生效範圍 | 持久性 | 優先序 |
|---|
| Session | ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; | 當前 Session | 斷線失效 | 最高 |
| Instance (Server) | init.ora / spfile: NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' | 全實例 (新連線) | 重啟生效 | 中 |
| Client (Windows) | 註冊表: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_\NLS_DATE_FORMAT | 該 Client 所有連線 | 永久 | 低 (被 Session 覆蓋) |
| Client (Linux/Unix) | 環境變數: export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' | 該 Shell 進程及子進程 | 進程結束失效 | 低 |
查看目前設定
-- Session 級
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
-- Instance 級
SHOW PARAMETER NLS_DATE_FORMAT;
-- 目前 SYSDATE 顯示樣貌
SELECT SYSDATE FROM DUAL;
Windows 註冊表永久修改步驟
- 開啟 `regedit`。
- 定位：`HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_` (如 `KEY_OraClient12Home1`)。
- 新增字串值 (String Value)：名稱 `NLS_DATE_FORMAT`，數值 `YYYY-MM-DD HH24:MI:SS`。
- 重新啟動應用/連線池生效。
同理可設定： `NLS_TIMESTAMP_FORMAT`、`NLS_TIMESTAMP_TZ_FORMAT`、`NLS_NUMERIC_CHARACTERS` (如 `.,` 歐洲格式)。
NLS_TERRITORY 關聯影響 (隱含預設值來源)
-- 查看 NLS_TERRITORY 決定的預設日期/數字格式
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_TERRITORY','NLS_DATE_FORMAT','NLS_NUMERIC_CHARACTERS');
-- 修改 Territory 會連帶改變日期格式、週起始日、貨幣符號等
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- 預設 MM/DD/YYYY
ALTER SESSION SET NLS_TERRITORY = 'TAIWAN'; -- 預設 YYYY/MM/DD
最佳實務建議
- 應用層統一使用 ISO 8601： `YYYY-MM-DDTHH24:MI:SS` (含 `T` 分隔) 或 `YYYY-MM-DD HH24:MI:SS`，避免歧義。
- 資料庫層不依賴隱含格式： 所有 SQL 明確使用 `TO_DATE`/`TO_CHAR` 指定格式，禁止 `WHERE date_col = '2024-01-01'` (隱含轉換)。
- Session 級統一設定： 連線池初始化執行 `ALTER SESSION SET NLS_DATE_FORMAT = ...`，確保該連線所有操作一致。
- 避免修改 Instance 級 NLS_DATE_FORMAT： 影響面過廣，易導致既有批次/報表錯誤。
- 時區處理： 使用 `TIMESTAMP WITH TIME ZONE`、`TIMESTAMP WITH LOCAL TIME ZONE` 型別，應用層統一 UTC 儲存。
常見錯誤與對策
| 錯誤 | 原因 | 修正 |
|---|
| ORA-01861: literal does not match format string | 字串與格式掩碼不符 | 對齊 TO_DATE 格式掩碼與實際字串 |
| ORA-01843: not a valid month | 月份值 > 12 或格式錯誤 (DD/MM vs MM/DD) | 確認 NLS_TERRITORY 或明確指定格式 |
| ORA-01830: date format picture ends before converting entire input string | 格式掩碼比字串短 | 補齊格式掩碼 (含時間部分) |
| 跨環境日期顯示不一致 | Client/Server NLS_DATE_FORMAT 不同 | 應用層統一 TO_CHAR 輸出格式 |
日期格式化的核心原則：**明確優於隱含，應用層統一，資料庫層不設預設**。掌握三層設定優先序與格式元素，即可徹底消除日期格式困擾。
