Oracle TO_DATE / TO_CHAR 日期格式完整參考與實戰技巧

一、核心格式元素速查表

以 2007-11-02 13:45:25 為例：

二、常用轉換範例

1. 日期 ⇄ 字串

-- 日期轉字串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS now_time FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY') AS yr, TO_CHAR(SYSDATE, 'MM') AS mon 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('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

-- Session 級設定語言
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

3. 兩日期天數差

SELECT FLOOR(SYSDATE - TO_DATE('20020405', 'YYYYMMDD')) FROM dual;

4. 處理 NULL 日期

SELECT id, active_date FROM table1
UNION
SELECT 1, TO_DATE(NULL) FROM dual; -- 必須用 TO_DATE(NULL)

5. 月份差

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...

6. 下一個指定週幾

-- NEXT_DAY(date, day) : day 可為 1(日)-7(六) 或 名稱
SELECT NEXT_DAY(SYSDATE, 6) FROM dual; -- 下一個星期五
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;

三、進階實戰技巧

1. 取得本月第一天/最後一天

SELECT 
    TRUNC(SYSDATE, 'MM') AS first_day_this_month,
    LAST_DAY(TRUNC(SYSDATE, 'MM')) AS last_day_this_month,
    TRUNC(TRUNC(SYSDATE, 'MM') - 1, 'MM') AS first_day_last_month,
    TRUNC(SYSDATE, 'MM') - 1/86400 AS last_day_last_month
FROM dual;

2. 計算年齡/年月日差

SELECT 
    TRUNC(MONTHS_BETWEEN(newer, older)/12) AS years,
    MOD(TRUNC(MONTHS_BETWEEN(newer, older)), 12) AS months,
    TRUNC(newer - ADD_MONTHS(older, TRUNC(MONTHS_BETWEEN(newer, older)/12)*12 + MOD(TRUNC(MONTHS_BETWEEN(newer, older)), 12))) AS days
FROM (
    SELECT HIREDATE AS older, ADD_MONTHS(HIREDATE, ROWNUM)+ROWNUM AS newer FROM EMP
);

3. 閏年判斷

-- 2 月最後一天若為 29 則為閏年
SELECT TO_CHAR(LAST_DAY(TO_DATE('02' || :year, 'MMYYYY')), 'DD') FROM dual;

4. 時區轉換

SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'EST'), 'DD/MM/YYYY HH:MI:SS'), SYSDATE FROM dual;

5. ROUND / TRUNC 日期截斷

SELECT 
    ROUND(SYSDATE) AS r_day,      -- 最近午夜
    ROUND(SYSDATE, 'YEAR') AS r_year,  -- 1/1 午夜
    ROUND(SYSDATE, 'MONTH') AS r_mon,  -- 1 號午夜
    TRUNC(SYSDATE) AS t_day,      -- 今日 00:00:00
    TRUNC(SYSDATE, 'MONTH') AS t_mon   -- 本月 1 號 00:00:00
FROM dual;

6. 取得小時數

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 02:38:40') FROM dual; -- 2
SELECT TO_CHAR(SYSDATE, 'HH24') FROM dual; -- 24制字串

四、常見坑點與避雷指南

1. 格式字串大小寫敏感：mm 是分，MM 才是月。
2. NLS 設定影響隱式轉換：生產環境務必顯式指定格式避免跨 Session/資料庫不一致。
3. BETWEEN date1 AND date2 包含邊界，但若含時間元件，2001-12-31 12:00 會被排除，建議用 TRUNC 或 >= AND <。
4. YYYY vs RRRR：兩位年輸入時 RRRR 會依當前世紀自動推斷（50 年分界），避免 2000 年問題。

Oracle 日期運算以「天」為單位，加減年月請用 ADD_MONTHS、MONTHS_BETWEEN，切勿直接乘 365/30，以免閏年、大小月導致誤差。