Oracle 日期時間函數大全：TO_DATE/TO_CHAR 轉換、間隔計算、時區處理、閏年判斷 26 實用範例

Oracle 日期時間處理是開發與報表的高頻需求。本文彙總 26 個實戰範例，覆蓋格式轉換、組件提取、間隔運算、特殊條件查詢、時區切換、閏年判斷、RRRR/YYYY 差異等核心場景，附帶可直接執行的 SQL，適合作為速查手冊收藏。

1. 基礎格式轉換

-- 日期 → 字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS now_time FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY')  AS now_year  FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MM')    AS now_month FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD')    AS now_day   FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24')  AS now_hour  FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MI')    AS now_min   FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'SS')    AS now_sec   FROM DUAL;

-- 字符串 → 日期
SELECT TO_DATE('2004-05-07 13:23:44', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2. 進階轉換技巧

-- 數字轉羅馬數字拼寫
SELECT TO_CHAR(TO_DATE(222, 'J'), 'Jsp') FROM DUAL;  -- Two Hundred Twenty-Two

-- 獲取星期幾（支持多語言）
SELECT TO_CHAR(TO_DATE('2002-08-26', 'YYYY-MM-DD'), 'DAY') FROM DUAL;           -- 星期一
SELECT TO_CHAR(TO_DATE('2002-08-26', 'YYYY-MM-DD'), 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL; -- MONDAY
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

3. 日期間隔運算

-- 兩日期天數差
SELECT FLOOR(SYSDATE - TO_DATE('20020405', 'YYYYMMDD')) FROM DUAL;

-- 月份差
SELECT MONTHS_BETWEEN(TO_DATE('01-31-1999', 'MM-DD-YYYY'), TO_DATE('12-31-1998', 'MM-DD-YYYY')) FROM DUAL; -- 1
SELECT MONTHS_BETWEEN(TO_DATE('02-01-1999', 'MM-DD-YYYY'), TO_DATE('12-31-1998', 'MM-DD-YYYY')) FROM DUAL; -- 1.032...

-- 年月日拆解（精確到日）
SELECT TRUNC(MONTHS_BETWEEN(newer, older)/12) AS years,
       MOD(TRUNC(MONTHS_BETWEEN(newer, older)), 12) AS months,
       TRUNC(newer - ADD_MONTHS(older, years*12+months)) AS days
FROM (SELECT HIREDATE older, ADD_MONTHS(HIREDATE, ROWNUM)+ROWNUM newer FROM EMP);

4. 特殊邊界處理

-- NULL 日期處理
SELECT id, active_date FROM table1
UNION
SELECT 1, TO_DATE(NULL) FROM DUAL;

-- 月末日期計算（解決月份天數不定）
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1, -2), 'YYYYMMDD'), LAST_DAY(SYSDATE) FROM DUAL;

-- 本年總天數（含閏年判斷）
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - TRUNC(SYSDATE, 'YEAR') FROM DUAL;
-- 閏年判斷：TO_CHAR(LAST_DAY(TO_DATE('02'||:year, 'MMYYYY')), 'DD') = '29' 即閏年

-- RRRR vs YYYY 兩位年份解析差異
-- YYYY: '99'→0099, '01'→0001
-- RRRR: '99'→1999, '01'→2001

5. 時區與精細時間操作

-- 時區轉換
SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'EST'), 'DD/MM/YYYY HH:MI:SS'), SYSDATE FROM DUAL;

-- 5 分鐘對齊（取當前時間向下取整到最近 5 分鐘）
SELECT TO_DATE(FLOOR(TO_CHAR(SYSDATE, 'SSSSS')/300)*300, 'SSSSS'), TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;

-- 年內第幾天
SELECT TO_CHAR(SYSDATE, 'DDD'), SYSDATE FROM DUAL;

-- 時分秒毫秒拆解
SELECT TRUNC(SYSDATE) AS days,
       SYSDATE - TRUNC(SYSDATE) AS frac,
       TRUNC((SYSDATE-TRUNC(SYSDATE))*24) AS hours,
       TRUNC((SYSDATE-TRUNC(SYSDATE))*24*60) % 60 AS minutes,
       TRUNC((SYSDATE-TRUNC(SYSDATE))*24*60*60) % 60 AS seconds,
       TRUNC((SYSDATE-TRUNC(SYSDATE))*24*60*60*100) % 100 AS msecs
FROM DUAL;

6. ROUND/TRUNC 日期取整

SELECT SYSDATE s1,
       ROUND(SYSDATE) s2,
       ROUND(SYSDATE, 'YEAR') yr,
       ROUND(SYSDATE, 'MONTH') mon,
       ROUND(SYSDATE, 'DAY') day
FROM DUAL;

SELECT SYSDATE s1,
       TRUNC(SYSDATE) s2,
       TRUNC(SYSDATE, 'YEAR') yr,
       TRUNC(SYSDATE, 'MONTH') mon,
       TRUNC(SYSDATE, 'DAY') day
FROM DUAL;

7. 極值與時間差換算

-- 最大日期
SELECT GREATEST('01-JAN-04', '04-JAN-04', '10-FEB-04') FROM DUAL;

-- 時間差換算（以天為單位）
SELECT FLOOR((SYSDATE - TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS'))/365) AS years FROM DUAL;
SELECT CEIL(MONTHS_BETWEEN(SYSDATE, TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS'))) AS months FROM DUAL;
SELECT FLOOR(SYSDATE - TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS')) AS days FROM DUAL;
SELECT FLOOR((SYSDATE - TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS'))*24) AS hours FROM DUAL;
SELECT FLOOR((SYSDATE - TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS'))*24*60) AS minutes FROM DUAL;
SELECT FLOOR((SYSDATE - TO_DATE('2007-11-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS'))*24*60*60) AS seconds FROM DUAL;

8. 日期加減運算

-- +n 年/月/日/時/分/秒
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') orig,
       TO_CHAR(SYSDATE + n*365, 'YYYY-MM-DD HH24:MI:SS') add_years,
       TO_CHAR(ADD_MONTHS(SYSDATE, n), 'YYYY-MM-DD HH24:MI:SS') add_months,
       TO_CHAR(SYSDATE + n, 'YYYY-MM-DD HH24:MI:SS') add_days,
       TO_CHAR(SYSDATE + n/24, 'YYYY-MM-DD HH24:MI:SS') add_hours,
       TO_CHAR(SYSDATE + n/24/60, 'YYYY-MM-DD HH24:MI:SS') add_mins,
       TO_CHAR(SYSDATE + n/24/60/60, 'YYYY-MM-DD HH24:MI:SS') add_secs
FROM DUAL;

9. 月首尾日期

SELECT TRUNC(TRUNC(SYSDATE, 'MONTH') - 1, 'MONTH') AS first_day_last_month,
       TRUNC(SYSDATE, 'MONTH') - 1/86400 AS last_day_last_month,
       TRUNC(SYSDATE, 'MONTH') AS first_day_cur_month,
       LAST_DAY(TRUNC(SYSDATE, 'MONTH')) + 1 - 1/86400 AS last_day_cur_month
FROM DUAL;

10. 排序技巧：按日期字符串排序

SELECT * FROM tabname
ORDER BY DECODE(mode, 'FIFO', 1, -1) * TO_CHAR(rq, 'YYYYMMDDHH24MISS');

收藏建議

將常用片段保存為代碼片段或建立包 PKG_DATE_UTIL 封裝通用邏輯，提升開發效率並統一格式標準。