Oracle TO_DATE/TO_CHAR 日期轉換完全指南：格式模型、常見陷阱與實戰技巧

日期時間處理是資料庫開發的高頻痛點。Oracle 的 TO_DATE（字串→日期）、TO_CHAR（日期→字串）雖功能強大，但格式模型對大小寫不敏感、分鐘用 MI 非 MM、24 小時制用 HH24 等細節極易踩坑。本文系統整理格式元素對照表、常見錯誤與解法、間隔運算、毫秒處理、效能測量等實戰技巧。

核心格式元素對照表

經典陷阱：MM vs MI

-- 錯誤：MM 被視為月份，導致 "format code appears twice"
SELECT TO_DATE('2005-01-01 13:14:20', 'yyyy-MM-dd HH24:mm:ss') FROM dual;
-- ORA-01810: format code appears twice

-- 正確：分鐘用 MI
SELECT TO_DATE('2005-01-01 13:14:20', 'yyyy-MM-dd HH24:mi:ss') FROM dual;
-- 2005-01-01 13:14:20

原因： Oracle 格式模型**不分大小寫**，MM (月) 與 mm (月) 衝突，必須用 MI 表分鐘。

TO_CHAR 輸出控制：FM 填充模式

-- 預設左補零/空格
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;  -- 2024-01-05 03:07:09

-- FM 移除前導零/空格
SELECT TO_CHAR(SYSDATE, 'FMYYYY-MM-DD FMHH24:MI:SS') FROM dual; -- 2024-1-5 3:7:9

-- 貨幣/數字格式同理
SELECT TO_CHAR(1234.5, 'FML99G999D99') FROM dual; -- $1,234.50 (依 NLS_TERRITORY)

日期間隔運算（INTERVAL）

-- 減去 7 分鐘/小時/天/月/年
SELECT SYSDATE - INTERVAL '7' MINUTE FROM dual;
SELECT SYSDATE - INTERVAL '7' HOUR   FROM dual;
SELECT SYSDATE - INTERVAL '7' DAY    FROM dual;
SELECT SYSDATE - INTERVAL '7' MONTH  FROM dual;
SELECT SYSDATE - INTERVAL '7' YEAR   FROM dual;

-- 間隔乘法
SELECT SYSDATE - 8 * INTERVAL '2' HOUR FROM dual;  -- 減 16 小時

-- 加法同理
SELECT SYSDATE + INTERVAL '3' DAY FROM dual;

優點：語意清晰、自動處理月份天數差異、支援年月間隔（INTERVAL '1-6' YEAR TO MONTH）。

TRUNC / ROUND 日期截取與四捨五入

-- 截取到年/月/日/時/分開頭
SELECT TRUNC(SYSDATE, 'YEAR')  FROM dual;  -- 2024-01-01 00:00:00
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;  -- 2024-01-01 00:00:00
SELECT TRUNC(SYSDATE, 'DAY')   FROM dual;  -- 本週週一 00:00:00
SELECT TRUNC(SYSDATE, 'HH')    FROM dual;  -- 當前小時 00 分
SELECT TRUNC(SYSDATE)          FROM dual;  -- 當天 00:00:00

-- ROUND 到最近邊界
SELECT ROUND(SYSDATE, 'MONTH') FROM dual;  -- 15 號後進入下月

毫秒/微秒級時間戳記

-- CURRENT_TIMESTAMP 回傳 TIMESTAMP WITH TIME ZONE
SELECT TO_CHAR(CURRENT_TIMESTAMP(6), 'YYYY-MM-DD HH24:MI:SS.FF6') FROM dual;
-- 2024-01-15 14:30:45.123456

-- 僅取秒.微秒
SELECT TO_CHAR(CURRENT_TIMESTAMP(9), 'MI:SS.FF9') FROM dual;

CURRENT_TIMESTAMP(precision) 精度 0-9，預設 6（微秒）。

PL/SQL 執行時間測量（毫秒級）

DECLARE
  l_start NUMBER := DBMS_UTILITY.GET_TIME;  -- 百分之一秒
  l_dummy ALL_OBJECTS.OBJECT_NAME%TYPE;
  l_rc    SYS_REFCURSOR;
BEGIN
  FOR i IN 1..1000 LOOP
    OPEN l_rc FOR 'SELECT object_name FROM all_objects WHERE object_id = ' || i;
    FETCH l_rc INTO l_dummy;
    CLOSE l_rc;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('耗時: ' || ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2) || ' 秒');
END;
/
-- 輸出: 耗時: 1.23 秒

DBMS_UTILITY.GET_TIME 回傳百分之一秒，除以 100 得秒，精度約 10ms。更高精度可用 DBMS_UTILITY.GET_CPU_TIME 或 Java System.nanoTime()。

字串→日期最佳實踐清單

1. 明確指定格式：永不依賴 NLS_DATE_FORMAT 隱性轉換。
2. 統一用 24 小時制：HH24 避免 AM/PM 歧義。
3. 分鐘務必用 MI：寫死在代碼規範與 Code Review 清單中。
4. 跨時區系統用 TIMESTAMP WITH TIME ZONE：避免夏令時/時區導致邏輯錯誤。
5. 批量載入用外部表或 SQL*Loader：指定 DATE_FORMAT 參數，避免逐行 TO_DATE 開銷。

掌握這套日期轉換知識體系，能從根源消除 90% 以上的日期處理 Bug，顯著提升開發效率與系統穩定性。