核心格式元素對照表
| 元素 | 含義 | 範例輸出 |
|---|
| YYYY | 4 位年 | 2024 |
| MM | 月 (01-12) | 01 |
| MON | 月縮寫 | JAN |
| MONTH | 月全名 | JANUARY |
| DD | 月內第幾天 | 15 |
| DDD | 年內第幾天 | 045 |
| DY | 星期縮寫 | MON |
| DAY | 星期全名 | MONDAY |
| HH24 | 24 小時制 | 13 |
| HH12 / HH | 12 小時制 | 01 |
| MI | 分 | 30 |
| SS | 秒 | 45 |
| FF | 小數秒 | .123456 |
1. 日期 → 字串 (TO_CHAR)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 2024-01-15 13:30:45
SELECT TO_CHAR(SYSDATE, 'YYYY-DDD HH:MI:SS') FROM dual;
-- 2024-015 01:30:45
SELECT TO_CHAR(SYSDATE, 'YYYY-MM IW-D HH:MI:SS') FROM dual;
-- 2024-01 03-1 01:30:45 (ISO 週、週內天)
2. 字串 → 日期 (TO_DATE)
SELECT TO_DATE('2024-01-15 13:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
3. 日期間隔運算 (INTERVAL)
-- 當前時間減 7 分鐘
SELECT SYSDATE, 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; -- 減 16 小時
4. 常用日期函數速查
| 函數 | 用途 | 範例 |
|---|
| SYSDATE | 資料庫伺服器當前日期時間 | SELECT SYSDATE FROM dual; |
| SYSTIMESTAMP | 含時區與小數秒 | SELECT SYSTIMESTAMP FROM dual; |
| ADD_MONTHS(d, n) | 加減月份 (自動處理月底) | ADD_MONTHS(SYSDATE, -3) |
| MONTHS_BETWEEN(d1, d2) | 兩日期月差 | MONTHS_BETWEEN(SYSDATE, hire_date) |
| NEXT_DAY(d, 'MONDAY') | 下一個指定星期 | NEXT_DAY(SYSDATE, 'FRIDAY') |
| LAST_DAY(d) | 該月最後一天 | LAST_DAY(SYSDATE) |
| ROUND(d, 'MONTH') | 日期四捨五入到月/年 | ROUND(SYSDATE, 'MONTH') |
| TRUNC(d, 'MM') | 日期截斷到月/年 | TRUNC(SYSDATE, 'YYYY') |
| EXTRACT(YEAR FROM d) | 擷取年/月/日/時/分/秒 | EXTRACT(MONTH FROM SYSDATE) |
5. 時區與 TIMESTAMP (11g+)
-- 帶時區時間戳
SELECT SYSTIMESTAMP FROM dual;
-- 2024-01-15 13:30:45.123456 +08:00
-- 時區轉換
SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Taipei' FROM dual;
避坑提醒
- 格式字串大小寫敏感：
MM (月) vs MI (分)，HH24 vs HH12。
TO_DATE 不指定時間部分預設 00:00:00，比較含時間欄位需注意。INTERVAL 單數/複數皆可：'1' DAY = '1' DAYS。- 跨時區應用統一儲存
TIMESTAMP WITH TIME ZONE，顯示時再轉換。
