Oracle 日期時間函數完全指南：SYSDATE、TO_CHAR、TO_DATE、TO_NUMBER 實戰

精通 Oracle 日期格式化與型別轉換核心函數

日期時間處理是 SQL 開發高頻需求。本文系統整理 SYSDATE 取得系統時間、TO_CHAR 格式化輸出、TO_DATE 字串轉日期、TO_NUMBER 字串轉數字的完整語法、格式元素對照表、常見陷阱與實戰範例。

核心函數速查

日期格式元素完整對照

實戰範例集

常見日期格式化

-- 完整時間 (24小時制)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;  -- 2024-01-15 14:30:00

-- 短日期
SELECT TO_CHAR(SYSDATE, 'YY-MM-DD') FROM DUAL;  -- 24-01-15

-- 帶中文/自定義文字
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM DUAL;  -- 2024年01月15日

字串轉日期 (注意 HH24 vs HH12)

-- 正確：24小時制配合 HH24
SELECT TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 錯誤：14 超過 12小時制範圍
SELECT TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH12:MI:SS') FROM DUAL; -- ORA-01821

數字格式化

-- 貨幣格式
SELECT TO_CHAR(1000000, '$99,999,999.99') FROM DUAL;  -- $1,000,000.00
SELECT TO_CHAR(1000000, 'L99,999,999.99') FROM DUAL;  -- 本地貨幣符號 (如 NT$)

-- 截斷小數
SELECT TRUNC(TO_NUMBER('1000000.123'), 2) FROM DUAL;  -- 1000000.12

日期運算 (INTERVAL 語法 9i+)

-- 減去 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 '7' HOUR FROM DUAL;

關鍵避坑指南

• MI 不是 MM：分鐘用 MI，月份用 MM，寫錯會導致格式錯位
• HH24 vs HH12：24小時制必須用 HH24，HH12 只接受 1-12
• NLS 設定影響：TO_DATE 無格式遮罩時依賴 NLS_DATE_FORMAT，跨環境請永遠指定格式
• DUAL 偽表：無實體表查詢時使用，任何使用者皆可存取
• 時區敏感：SYSDATE 回傳資料庫伺服器時區，跨時區應用建議用 SYSTIMESTAMP + TIMEZONE 處理

熟練日期格式元素與轉換函數，能消除 90% 的日期處理 Bug。