日期格式元素速查表
| 元素 | 說明 | 範例輸出 |
| YYYY | 4 位元年份 | 2024 |
| YY | 2 位元年份 | 24 |
| MM | 月份 (01-12) | 03 |
| MON | 月份縮寫 | MAR |
| MONTH | 月份全名 | MARCH |
| DD | 日期 (01-31) | 12 |
| DAY | 星期全名 | FRIDAY |
| DY | 星期縮寫 | FRI |
| DDSPTH | 序數拼寫 | TWELFTH |
| HH24 | 24 小時制 (00-23) | 14 |
| HH12 | 12 小時制 (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'
解決方案
- 應用層統一用 ISO 格式:
YYYY-MM-DD HH24:MI:SS(無歧義)
- 明確指定格式模板: 永遠使用
TO_DATE(:val, 'YYYY-MM-DD'),不依賴隱性轉換
- 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(含時區、毫秒) |
最佳實踐總結
- 資料庫欄位永遠用
DATE 或 TIMESTAMP 型別,絕不存字串
- 所有轉換明確指定格式模板,禁止隱性轉換
- 應用傳參使用 Bind Variable,由驅動程式處理型別轉換
- 跨時區系統統一儲存
TIMESTAMP WITH TIME ZONE,顯示時再轉換
- 定期檢查
NLS_SESSION_PARAMETERS 確認環境一致性