Oracle 日期處理的核心在於兩個轉換函數的配合使用：
TO_DATE(string, format_mask [, nls_language])
-- 日期轉換
TO_DATE('2004-11-27', 'YYYY-MM-DD')
-- 日期時間轉換
TO_DATE('2004-11-27 13:34:43', 'YYYY-MM-DD HH24:MI:SS')
-- 含毫秒
TO_DATE('2024-01-15 14:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3')
字串中的分隔符、長度、大小寫皆需與格式模式一致，否則拋出 ORA-01861 或 ORA-01843 錯誤。
| 格式元素 | 含義 | 範例輸出 |
|---|---|---|
YYYY | 四位數年份 | 2024 |
YYY / YY / Y | 年份後三/二/一位 | 024 / 24 / 4 |
MM | 兩位數月份 (01-12) | 01 |
MONTH | 完整月份名稱（右補空格至 9 字元） | JANUARY |
MON | 月份縮寫（三字元） | JAN |
WW | 年間第幾週 (1-53) | 03 |
D | 週內第幾天 (1-7，週日=1) | 2 |
DD | 月內第幾天 (01-31) | 15 |
DDD | 年間第幾天 (001-366) | 015 |
DAY | 完整星期名稱（右補空格至 9 字元） | MONDAY |
HH / HH12 | 12 小時制小時 (01-12) | 02 |
HH24 | 24 小時制小時 (00-23) | 14 |
MI | 分鐘 (00-59) | 30 |
SS | 秒 (00-59) | 45 |
SSSSS | 午夜至今總秒數 (0-86399) | 52245 |
FF / FF1..FF9 | 小數秒（毫秒/微秒/奈秒） | .123456 |
AM / PM | 上午/下午標記 | PM |
TO_CHAR(date_value, format_mask [, nls_language])
-- 標準日期時間
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 2024-01-15 14:30:45
-- 中文日期
SELECT TO_CHAR(SYSDATE, 'YYYY年MM月DD日', 'NLS_DATE_LANGUAGE=TRADITIONAL CHINESE') FROM dual;
-- 2024年01月15日
-- 週幾顯示
SELECT TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=TRADITIONAL CHINESE') FROM dual;
-- 星期一
-- 自訂格式
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI') FROM dual;
-- 2024/01/15 14:30
預設日期語言由 NLS_DATE_LANGUAGE 或 NLS_TERRITORY 決定，可在 Session 層級覆蓋：
ALTER SESSION SET NLS_DATE_LANGUAGE = 'TRADITIONAL CHINESE';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- 計算下個月第一天
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1), 'YYYY-MM-DD') FROM dual;
-- 字串比較陷阱：勿直接比較日期字串
-- 錯誤：WHERE date_col > '2024-01-01' -- 隱含轉換風險
-- 正確：WHERE date_col > TO_DATE('2024-01-01', 'YYYY-MM-DD')
-- 使用 CASE 或 COALESCE 處理不確定格式
SELECT TO_DATE(date_str,
CASE
WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN 'YYYY-MM-DD HH24:MI:SS'
WHEN REGEXP_LIKE(date_str, '^\d{4}/\d{2}/\d{2}$') THEN 'YYYY/MM/DD'
ELSE 'YYYY-MM-DD'
END)
FROM staging_table;
| 錯誤代碼 | 原因 | 解決方案 |
|---|---|---|
| ORA-01843 | 非有效月份/日期數值 | 檢查格式模式與資料是否對應，月份 01-12、日期 01-31 |
| ORA-01861 | 字面值與格式字串不匹配 | 確認分隔符、長度、大小寫完全一致 |
| ORA-01858 | 非數值字符出現在數值位置 | 資料含雜訊，需預清洗或用正則過濾 |
| ORA-01830 | 格式模式圖片過長 | 格式字串超過限制，簡化模式 |
DATE '2024-01-15'、TIMESTAMP '2024-01-15 14:30:45'TIMESTAMP 型別與 FF 格式元素TO_DATE 與 TO_CHAR 是 Oracle 日期處理的基石。熟練掌握格式元素對照表、理解 NLS 參數影響、避開隱含轉換陷阱，是每位 Oracle 開發者的必修功課。建議建立專案級的日期格式規範文件，統一團隊開發標準。
