Oracle 日期轉換函數速查手冊：EXTRACT、TO_CHAR、TO_DATE、TRUNC 實戰用法

前言

日期處理是 Oracle 開發最高頻操作。本文匯總 EXTRACT、TO_CHAR、TO_DATE、TRUNC 四大核心函數的語法、格式模型、常用場景與避坑指南，作為開發案頭速查手冊。

一、EXTRACT：提取日期時間組件 (推薦)

1. 語法

EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE }
         FROM { DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH LOCAL TIME ZONE | INTERVAL YEAR TO MONTH | INTERVAL DAY TO SECOND } )

2. 常用示例

-- 從 DATE 只能提取 YEAR/MONTH/DAY
SELECT EXTRACT(YEAR FROM SYSDATE) AS yr,
       EXTRACT(MONTH FROM SYSDATE) AS mon,
       EXTRACT(DAY FROM SYSDATE) AS day
FROM DUAL;

-- 從 TIMESTAMP 可提取完整組件
SELECT EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) AS hr,
       EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS mi
FROM DUAL;

3. 優勢

• 語義明確，類型安全
• 返回 NUMBER 類型，可直接參與數學運算
• 避免 TO_CHAR 隱式轉換風險

二、TO_CHAR：日期轉字符串 (格式化輸出)

1. 常用格式模型

2. 組合格式示例

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS dt,           -- 2024-01-15
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS dtm, -- 2024-01-15 14:30:45
       TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS euro_dt,       -- 15/01/2024
       TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') AS long_dt  -- January 15, 2024 (FM 去除填充空格)
FROM DUAL;

3. NLS 參數影響

-- 顯式指定語言避免環境差異
TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE=AMERICAN')  -- JANUARY
TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE') -- 星期一

三、TO_CHAR/TO_DATE：字符串轉日期

1. 基本語法

TO_DATE(char, fmt [, nls_params])

2. 常用轉換示例

-- 標準格式
TO_DATE('2024-01-15', 'YYYY-MM-DD')

-- 含時間
TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')

-- 歐洲格式
TO_DATE('15/01/2024', 'DD/MM/YYYY')

-- 格式模型不區分大小寫
TO_DATE('2024-01-15', 'yyyy-mm-dd')  -- 等效

-- 複雜格式
TO_DATE('Monday, January 15, 2024', 'DAY, MONTH DD, YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

3. 格式模型與輸入匹配規則

• FX 修飾符：強制精確匹配 TO_DATE('2024-1-15', 'FXYYYY-MM-DD') 會報錯 (月份需兩位)
• 缺失組件默認值：年→當前年、月→當前月、日→1、時分秒→0

四、TRUNC：日期截斷 (去除時間/對齊邊界)

1. 語法

TRUNC(date [, fmt])

2. 常用 fmt 值

3. 核心應用：索引友好範圍查詢

-- ❌ 殺死索引
WHERE TRUNC(create_time) = TRUNC(SYSDATE)

-- ✅ 索引友好
WHERE create_time >= TRUNC(SYSDATE)
  AND create_time < TRUNC(SYSDATE) + 1

五、綜合實戰場景

1. 判斷某天是星期幾

SELECT TO_CHAR(TO_DATE('2024-01-15', 'YYYY-MM-DD'), 'DAY') FROM DUAL;  -- MONDAY
SELECT TO_CHAR(TO_DATE('2024-01-15', 'YYYY-MM-DD'), 'DY') FROM DUAL;   -- MON

2. 獲取系統時間 (含時區)

SELECT SYSDATE FROM DUAL;                    -- 資料庫服務器時間 (DATE)
SELECT SYSTIMESTAMP FROM DUAL;               -- 含時區、毫秒 (TIMESTAMP WITH TIME ZONE)
SELECT CURRENT_DATE FROM DUAL;               -- 會話時區日期
SELECT CURRENT_TIMESTAMP FROM DUAL;          -- 會話時區時間戳

3. 日期運算輔助

-- ADD_MONTHS 處理月末
ADD_MONTHS(DATE '2024-01-31', 1)  -- 2024-02-29 (閏年)
ADD_MONTHS(DATE '2024-01-31', -1) -- 2023-12-31

-- 間隔運算 (10g+)
SYSDATE + INTERVAL '1' DAY
SYSDATE + INTERVAL '3' HOUR

六、避坑指南

結語

掌握 EXTRACT(提取)、TO_CHAR(格式化)、TO_DATE(解析)、TRUNC(截斷) 四大核心函數，熟記常用格式模型，堅守「顯式轉換、索引友好、NLS 顯式指定」三原則，即可從容應對 90% 以上日期處理場景。