核心日期函數速查表
| 函數 | 用途 | 語法範例 | 說明 |
|---|
| SYSDATE | 取得資料庫伺服器當前日期時間 | SELECT SYSDATE FROM dual; | 含時分秒，精確到秒 |
| CURRENT_DATE | 取得 Session 時區的當前日期 | SELECT CURRENT_DATE FROM dual; | 受 SESSIONTIMEZONE 影響 |
| SYSTIMESTAMP | 取得伺服器時間戳記 (含微秒/時區) | SELECT SYSTIMESTAMP FROM dual; | TIMESTAMP WITH TIME ZONE 型別 |
| TRUNC(date) | 截去時間部分，回傳午夜 00:00:00 | TRUNC(SYSDATE) | 常用於日期比較、分組 |
| ROUND(date) | 四捨五入到最近的日期/月/年 | ROUND(SYSDATE, 'MONTH') | 格式模型參見下表 |
| ADD_MONTHS | 加減月份 | ADD_MONTHS(SYSDATE, 3) | 負數為減月，自動處理月末 |
| MONTHS_BETWEEN | 計算兩日期相差月數 | MONTHS_BETWEEN(d1, d2) | d1 > d2 為正，含小數部分 |
| LAST_DAY | 取得該月最後一天 | LAST_DAY(SYSDATE) | 自動處理閏年 |
| NEXT_DAY | 取得指定週名之後的第一個日期 | NEXT_DAY(SYSDATE, 'FRIDAY') | 週名依 NLS_DATE_LANGUAGE |
| EXTRACT | 擷取日期欄位 (YEAR/MONTH/DAY/HOUR...) | EXTRACT(MONTH FROM SYSDATE) | 回傳 NUMBER |
| NEW_TIME | 跨時區轉換 (舊函數) | NEW_TIME(d, 'PST', 'EST') | 建議改用 TIMESTAMP WITH TIME ZONE |
| TO_CHAR | 日期轉字串 | TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') | 格式模型見下 |
| TO_DATE | 字串轉日期 | TO_DATE('2023-07-15', 'YYYY-MM-DD') | 格式模型見下 |
常用日期格式模型
| 模型 | 意義 | 範例輸出 |
|---|
| YYYY | 4 位數年份 | 2023 |
| MM | 2 位數月份 | 07 |
| MON | 月份縮寫 | JUL |
| DD | 月份中的第幾天 | 15 |
| DAY | 星期名稱 | SATURDAY |
| HH24 | 24 小時制小時 | 14 |
| MI | 分鐘 | 30 |
| SS | 秒 | 45 |
| FF3 | 毫秒 (3 位) | 123 |
| TZH:TZM | 時區位移 | +08:00 |
實用範例集
1. 本月第一天 / 最後一天
TRUNC(SYSDATE, 'MM') -- 本月 1 號 00:00:00
LAST_DAY(TRUNC(SYSDATE, 'MM')) -- 本月最後一天 00:00:00
2. 計算年齡 / 年齡分組
FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) AS age_years
TRUNC(MONTHS_BETWEEN(SYSDATE, birth_date) / 12, -1) * 10 AS age_group_10
3. 找出下個工作日 (排除週末)
CASE TO_CHAR(NEXT_DAY(dt, 'FRIDAY'), 'DY')
WHEN 'SAT' THEN NEXT_DAY(dt, 'MONDAY')
WHEN 'SUN' THEN NEXT_DAY(dt, 'MONDAY')
ELSE NEXT_DAY(dt, 'FRIDAY')
END
4. 時區轉換 (11g+ 推薦)
CAST(FROM_TZ(CAST(local_dt AS TIMESTAMP), 'Asia/Taipei')
AT TIME ZONE 'UTC' AS DATE) AS utc_dt
5. 日期區間產生器 (連續日期)
SELECT TRUNC(SYSDATE) + LEVEL - 1 AS dt
FROM dual
CONNECT BY LEVEL <= 30; -- 產生未來 30 天
常見陷阱與最佳實務
- 隱含轉換： 絕不依賴 `WHERE date_col = '15-JUL-23'`，務必用 `TO_DATE` 或 `DATE '2023-07-15'`
- 索引失效： `WHERE TRUNC(date_col) = TRUNC(SYSDATE)` 會導致索引無法使用，改用 `WHERE date_col >= TRUNC(SYSDATE) AND date_col < TRUNC(SYSDATE)+1`
- NLS 相依： `TO_CHAR` 月份名稱、星期名稱受 `NLS_DATE_LANGUAGE` 影響，跨環境請明確指定 `'NLS_DATE_LANGUAGE=AMERICAN'`
- 閏秒/夏令： 跨時區計算請改用 `TIMESTAMP WITH TIME ZONE` + `AT TIME ZONE`，避免 `NEW_TIME` 缺陷
