核心函數語法
TO_DATE(string, format_mask, nls_language)
TO_CHAR(date, format_mask, nls_language)
格式模型元素完整表
| 代碼 | 說明 | 範例輸出 |
|---|
| YYYY | 4 位年份 | 2024 |
| YY / Y | 後 2/1 位年份 | 24 / 4 |
| RRRR | 2 位年份自動推斷世紀 | 99→1999, 01→2001 |
| MM | 月份 (01-12) | 01 |
| MON / MONTH | 月份縮寫/全名 | JAN / JANUARY |
| DD | 月內天數 (01-31) | 15 |
| DDD | 年內天數 (1-366) | 045 |
| DY / DAY | 星期縮寫/全名 | MON / MONDAY |
| HH / HH12 | 12 小時制 (1-12) | 03 |
| HH24 | 24 小時制 (0-23) | 15 |
| MI | 分鐘 (0-59) | 30 |
| SS | 秒 (0-59) | 45 |
| FF / FFn | 小數秒 (微秒/奈秒) | .123456 |
| AM / PM | 上午/下午標記 | PM |
| TZD / TZH / TZM | 時區資訊 | CST / +08 / 00 |
常見錯誤與修正
ORA-01810: format code appears twice
-- ❌ 錯誤：MM 出現兩次 (月份 + 分鐘)
TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH:MM:SS')
-- ✅ 正確：分鐘用 MI
TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
Oracle 格式模型不區分大小寫，MM 同時匹配月份與分鐘。
ORA-01722: invalid number
-- ❌ 錯誤：HH 範圍 1-12，00 時無效
TO_DATE('2024-01-15 00:30:00', 'YYYY-MM-DD HH:MI:SS')
-- ✅ 正確：24 小時制用 HH24
TO_DATE('2024-01-15 00:30:00', 'YYYY-MM-DD HH24:MI:SS')
ORA-01843: not a valid month
-- ❌ 格式不符
TO_DATE('15-JAN-2024', 'YYYY-MM-DD')
-- ✅ 對應格式
TO_DATE('15-JAN-2024', 'DD-MON-YYYY')
實戰範例庫
-- 標準 ISO 格式
TO_DATE('2024-01-15T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS')
-- 中文日期
TO_DATE('2024年1月15日', 'YYYY"年"MM"月"DD"日"', 'NLS_DATE_LANGUAGE=TRADITIONAL CHINESE')
-- Unix Timestamp (秒)
TO_DATE('1970-01-01', 'YYYY-MM-DD') + (1705312200 / 86400)
-- 當月第一天/最後一天
TRUNC(SYSDATE, 'MM')
LAST_DAY(SYSDATE)
-- 下個週一
NEXT_DAY(SYSDATE, 'MONDAY')
-- 兩日期間隔 (天/月/年)
FLOOR(SYSDATE - TO_DATE('2024-01-01','YYYY-MM-DD'))
MONTHS_BETWEEN(SYSDATE, TO_DATE('2024-01-01','YYYY-MM-DD'))
NLS 參數影響
| 參數 | 影響 | Session 修改 |
|---|
| NLS_DATE_FORMAT | 隱含 TO_CHAR/TO_DATE 預設格式 | ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' |
| NLS_DATE_LANGUAGE | 月份/星期名稱語言 | ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN |
| NLS_TERRITORY | 週起始日、數字/貨幣格式 | ALTER SESSION SET NLS_TERRITORY=TAIWAN |
最佳實踐鐵律
- 永遠指定格式模型：禁止依賴 NLS 預設值
- Bind Variable 傳遞 DATE 型別：避免字串轉換開銷與錯誤
- 儲存用 DATE/TIMESTAMP：勿用 VARCHAR2 存日期
- 跨時區用 TIMESTAMP WITH TIME ZONE
- 批次轉換用 SQL*Loader/External Table 指定格式
