| 環境 | 預設格式 | 範例輸出 |
|---|
| 英文版 Oracle | DD-MON-RR | 01-JAN-23 |
| 中文版 Oracle | DD-MON-RR (月份顯示中文) | 21-8月-23 或 21-8月-03 |
| 會話修改 | ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; | 即時生效，僅影響當前會話 |
三、 字串 ↔ 日期 雙向轉換
1. TO_DATE：字串 → DATE
TO_DATE(string, format_mask)
-- 常用格式元素
YYYY 四位年 MM 月(01-12) MON 月縮寫 (JAN/JUL)
DD 日(01-31) HH24 時(00-23) MI 分(00-59)
SS 秒(00-59) RR 兩位年 (1950-2049 窗口)
實戰範例
CREATE TABLE student (name VARCHAR2(10) PRIMARY KEY, enrolldate DATE NOT NULL);
-- 多種格式皆可成功插入 (格式遮罩匹配即可)
INSERT INTO student VALUES ('mark', TO_DATE('08-21-2003', 'MM-DD-YYYY'));
INSERT INTO student VALUES ('mark1', TO_DATE('21-08-2003', 'DD-MM-YYYY'));
INSERT INTO student VALUES ('mark2', '21-8月-2003'); -- 符合 NLS_DATE_FORMAT 可無格式遮罩
INSERT INTO student VALUES ('mark3', '21-8月-03'); -- RR 兩位年自動推算世紀
2. TO_CHAR：DATE → 字串
TO_CHAR(date_value, format_mask)
-- 提取時間成分
SELECT TO_CHAR(enrolldate, 'HH24:MI:SS') AS enroll_time FROM student;
-- 完整格式化
SELECT TO_CHAR(enrolldate, 'YYYY-MM-DD HH24:MI:SS') AS full_dt FROM student;
四、 純時間插入的隱性預設日期陷阱
若 TO_DATE 僅提供時間成分，**系統自動補上「當月第一天」** 作為日期部分。
-- 假設系統當前 2003-08-21
INSERT INTO student VALUES ('mark5', TO_DATE('09:28:03', 'HH:MI:SS'));
-- 實際儲存：2003-08-01 09:28:03 (當月 1 號)
-- 修改系統日期為 2003-07-21
INSERT INTO student VALUES ('mark6', TO_DATE('09:28:03', 'HH:MI:SS'));
-- 實際儲存：2003-07-01 09:28:03
-- 24 小時制
INSERT INTO student VALUES ('mark8', TO_DATE('20:28:03', 'HH24:MI:SS'));
警示：此行為極易導致業務邏輯錯誤 (如以為存純時間，實際查詢含日期)。
最佳實務：明確指定完整日期時間
-- 選項 1：完整格式
TO_DATE('2003-08-21 09:28:03', 'YYYY-MM-DD HH24:MI:SS')
-- 選項 2：TRUNC(SYSDATE) + 時間 (若確需「今天」)
TRUNC(SYSDATE) + TO_DATE('09:28:03', 'HH24:MI:SS') - TRUNC(TO_DATE('09:28:03', 'HH24:MI:SS'))
五、 常用日期運算與函數
| 運算/函數 | 結果型別 | 範例 |
|---|
SYSDATE | DATE | 資料庫伺服器當前日期時間 |
SYSTIMESTAMP | TIMESTAMP WITH TIME ZONE | 含毫秒與時區 |
DATE + NUMBER | DATE | SYSDATE + 7 一週後 |
DATE - DATE | NUMBER (天數) | SYSDATE - hire_date 任職天數 |
ADD_MONTHS(d, n) | DATE | 月份加減，自動處理月底 |
MONTHS_BETWEEN(d1, d2) | NUMBER | 月份差 |
NEXT_DAY(d, 'FRIDAY') | DATE | 下一個周五 |
LAST_DAY(d) | DATE | 該月最後一天 |
TRUNC(d, 'MM') | DATE | 截斷至月初 00:00:00 |
ROUND(d, 'HH') | DATE | 四捨五入至小時 |
六、 JSP/Java 整合陷阱與解決方案
| 陷阱 | 現象 | 根因 | 解決方案 |
|---|
| java.sql.Date vs java.util.Date | 時間成分丟失 | java.sql.Date 繼承 java.util.Date 但 `toString()` 只顯示日期 | JDBC 讀取用 getTimestamp() 取 java.sql.Timestamp，或 getObject() 轉 LocalDateTime (JDBC 4.2+) |
| PreparedStatement setDate | 時間歸零 | setDate() 只傳日期部分 | 用 setTimestamp() 或 setObject(LocalDateTime) |
| NLS_DATE_FORMAT 不一致 | 字串轉換報錯 ORA-018xx | 應用端格式與 DB Session 格式不符 | **統一使用 PreparedStatement 繫結變數**，避免字串拼接 SQL |
| 時區差異 | 顯示時間偏移 | DB 伺服器時區 ≠ 應用伺服器時區 ≠ 客戶端時區 | 資料庫儲存 UTC (用 TIMESTAMP WITH TIME ZONE)，應用層統一轉換顯示 |
七、 避坑檢查清單
- ☐ 所有日期字串轉換**明確指定格式遮罩**，不依賴 NLS_DATE_FORMAT
- ☐ 純時間業務**改用 TIMESTAMP 或 INTERVAL DAY TO SECOND**，避免 DATE 隱性補齊
- ☐ JDBC 繫結**統一用 Timestamp/LocalDateTime**，禁用 java.sql.Date
- ☐ 日期運算優先用
ADD_MONTHS/TRUNC/ROUND，避免手算天數
- ☐ 歷史資料查詢注意 **世紀窗口 (RR vs YYYY)**，避免 2050 年問題
- ☐ 索引欄位若用函數 (如
TRUNC(date_col)) 必建 **Function-based Index**
Oracle DATE 型別「日期時間合一」的設計既是優勢也是陷阱。掌握 TO_DATE/TO_CHAR 雙向精確控制、識別純時間插入隱性補齊、統一 JDBC 繫結最佳實務，能消除 90% 日期相關的詭異 Bug。
- 上一篇文章： Oracle ROWNUM 與 ROWID 深度解析：偽欄位特性、分頁查詢陷阱與最佳實踐
- 下一篇文章： Oracle 死鎖進程診斷與終結標準流程：V$ 視圖定位、ALTER SYSTEM KILL、OS 層強制清理
Copyright ©
程式碼之家 All Rights Reserved
