核心陷阱：MI vs MM
Oracle SQL 不區分大小寫，MM（月份）與 mm（分鐘）會衝突。分鐘必須用 MI。
-- 錯誤：ORA-01810 format code appears twice
TO_DATE('2024-01-15 14:30:59', 'YYYY-MM-DD HH24:MM:SS')
-- 正確
TO_DATE('2024-01-15 14:30:59', 'YYYY-MM-DD HH24:MI:SS')
完整格式參數速查
| 參數 | 含義 | 範例 |
|---|
| YYYY | 4 位年份 | 2024 |
| MM | 2 位月份 | 01 |
| MON | 月份縮寫 | JAN |
| MONTH | 月份全名 | JANUARY |
| DD | 月內第幾天 | 15 |
| DDD | 年內第幾天 | 015 |
| DAY | 星期名稱 | MONDAY |
| DY | 星期縮寫 | MON |
| HH24 | 24 小時制 | 14 |
| HH / HH12 | 12 小時制 | 02 |
| MI | 分鐘 | 30 |
| SS | 秒 | 59 |
| FF / FFn | 小數秒（n 位，最多 9） | .123456 |
| IW | ISO 週數 | 03 |
| IYYY | ISO 年份 | 2024 |
日期時間間隔運算（10g+）
-- 減 7 分鐘
SELECT SYSDATE - INTERVAL '7' MINUTE FROM dual;
-- 減 7 小時
SELECT SYSDATE - INTERVAL '7' HOUR FROM dual;
-- 減 7 天
SELECT SYSDATE - INTERVAL '7' DAY FROM dual;
-- 減 7 月
SELECT SYSDATE - INTERVAL '7' MONTH FROM dual;
-- 減 7 年
SELECT SYSDATE - INTERVAL '7' YEAR FROM dual;
-- 間隔乘以純量
SELECT SYSDATE - 8 * INTERVAL '2' HOUR FROM dual;
字串轉日期與日期轉字串對照
| 操作 | 函數 | 範例 |
|---|
| 字串 → 日期 | TO_DATE(str, fmt) | TO_DATE('2024-01-15 14:30', 'YYYY-MM-DD HH24:MI') |
| 日期 → 字串 | TO_CHAR(dt, fmt) | TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') |
| 字串 → 時間戳記 | TO_TIMESTAMP(str, fmt) | TO_TIMESTAMP('2024-01-15 14:30:59.123', 'YYYY-MM-DD HH24:MI:SS.FF') |
| 時間戳記 → 字串 | TO_CHAR(ts, fmt) | TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF') |
TRUNC / ROUND 日期截斷與四捨五入
-- 截斷至年首
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;
-- 截斷至日（預設）
SELECT TRUNC(SYSDATE) FROM dual;
-- 四捨五入至月（15 號後進下月）
SELECT ROUND(SYSDATE, 'MM') FROM dual;
毫秒級時間戳記
-- 目前時間含毫秒（精度 5 位）
SELECT TO_CHAR(CURRENT_TIMESTAMP(5), 'DD-MON-YYYY HH24:MI:SS.FF') FROM dual;
-- 僅取分秒毫秒
SELECT TO_CHAR(CURRENT_TIMESTAMP(9), 'MI:SS.FF') FROM dual;
程式執行耗時測量（百分之一秒）
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) || ' seconds');
END;
/
避坑清單
- 分鐘用
MI，月份用 MM，切勿混淆。
HH = 12 小時制，HH24 = 24 小時制。- 隱含轉換風險：
WHERE dt = '2024-01-15' → 改用 DATE '2024-01-15' 或 TO_DATE(..., fmt)。
NLS_DATE_LANGUAGE 影響 MON/MONTH/DAY 解析，跨語言環境請明確指定或統一用數字格式。
