萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 日期處理完全指南:TO_DATE/TO_CHAR 格式、星期轉換、日期運算、NLS 參數

日期格式元素速查表

元素說明範例輸出
YYYY4 位元年份2024
YY2 位元年份24
MM月份 (01-12)03
MON月份縮寫MAR
MONTH月份全名MARCH
DD日期 (01-31)12
DAY星期全名FRIDAY
DY星期縮寫FRI
DDSPTH序數拼寫TWELFTH
HH2424 小時制 (00-23)14
HH1212 小時制 (01-12)02
MI分鐘 (00-59)30
SS秒 (00-59)45

核心轉換函數

TO_DATE:字串 → DATE


TO_DATE('2024-03-12 14:30:45', 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('12-MAR-24', 'DD-MON-RR')  -- RR 自動推斷世紀

TO_CHAR:DATE → 字串


TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')  -- 2024-03-12 14:30:45
TO_CHAR(SYSDATE, 'Day, Month DD, YYYY')    -- Friday, March 12, 2024
TO_CHAR(TO_DATE(222, 'J'), 'Jsp')          -- Two Hundred Twenty-Two (拼寫數字)

實用查詢範例

1. 判斷某日期是星期幾


SELECT TO_CHAR(TO_DATE('2002-08-26', 'YYYY-MM-DD'), 'Day') FROM dual;
-- 輸出:Monday

-- 指定語言
SELECT TO_CHAR(TO_DATE('2002-08-26', 'YYYY-MM-DD'), 'Day', 'NLS_DATE_LANGUAGE=AMERICAN') FROM dual;
-- 輸出:MONDAY

2. 設定 Session 語言環境


ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
-- 或在 TO_DATE/TO_CHAR 直接指定第三參數

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),直接 NULL 會導致資料型別不符

5. BETWEEN 日期範圍陷阱


-- 錯誤:不含 12/31 當天 12:00 之後的資料
WHERE a_date BETWEEN TO_DATE('20011201','YYYYMMDD') AND TO_DATE('20011231','YYYYMMDD')

-- 正確:明確指定時間或用 < 翌日
WHERE a_date >= TO_DATE('20011201','YYYYMMDD')
  AND a_date <  TO_DATE('20020101','YYYYMMDD')

NLS 參數與格式衝突

輸入格式取決於資料庫/Session 的 NLS_DATE_FORMAT、NLS_TERRITORY、NLS_LANGUAGE。

檢視目前參數


SELECT * FROM nls_session_parameters;   -- 目前 Session
SELECT * FROM v$nls_parameters;         -- 資料庫級

常見衝突場景

  • 用戶端 NLS_LANG=AMERICAN_AMERICA.ZHT16MSWIN950,資料庫 NLS_TERRITORY=TAIWAN → 日期格式不符
  • 應用傳入 '01-一月-24' 但資料庫預期 '01-JAN-24'

解決方案

  1. 應用層統一用 ISO 格式: YYYY-MM-DD HH24:MI:SS(無歧義)
  2. 明確指定格式模板: 永遠使用 TO_DATE(:val, 'YYYY-MM-DD'),不依賴隱性轉換
  3. Session 層級設定: 連線建立後立即執行 ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'

進階日期運算

需求語法
加減天數SYSDATE + 7、SYSDATE - 30
加減月份ADD_MONTHS(SYSDATE, 3)、ADD_MONTHS(SYSDATE, -1)
月份最後一天LAST_DAY(SYSDATE)
下一個指定星期NEXT_DAY(SYSDATE, 'FRIDAY')
兩日期月份差MONTHS_BETWEEN(date1, date2)
截取到月/年TRUNC(SYSDATE, 'MM')、TRUNC(SYSDATE, 'YYYY')
取得當前時間戳SYSTIMESTAMP(含時區、毫秒)

最佳實踐總結

  1. 資料庫欄位永遠用 DATE 或 TIMESTAMP 型別,絕不存字串
  2. 所有轉換明確指定格式模板,禁止隱性轉換
  3. 應用傳參使用 Bind Variable,由驅動程式處理型別轉換
  4. 跨時區系統統一儲存 TIMESTAMP WITH TIME ZONE,顯示時再轉換
  5. 定期檢查 NLS_SESSION_PARAMETERS 確認環境一致性
copyright © 萬盛學電腦網 all rights reserved