Oracle 日期時間操作實戰：SYSDATE 插入、TO_DATE 格式容錯與精確範圍查詢技巧

插入系統時間：SYSDATE 直接用

INSERT INTO test VALUES (2, 'cba', SYSDATE);
-- 自動記錄執行當下的日期時間 (精確到秒)

TO_DATE 格式模型的「寬容特性」

Oracle TO_DATE 僅要求格式字串與輸入字串的「元素對應」正確，分隔符號可任意混用：

-- 以下全部合法且結果相同
TO_DATE('2009/11/01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('2009;11;01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('2009,11/01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('2009-11-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
TO_DATE('2009/11-01 12:00:00', 'YYYY;MM-DD HH24:MI:SS')

原理：格式模型定義「年/月/日/時/分/秒」的提取位置，非逐字符匹配分隔符。

精確日期範圍查詢：半開區間 [start, end) 模式

需求：查詢 2009-11-01 全天資料 (含 00:00:00 至 23:59:59)

推薦寫法：大於等於起始，小於隔日開始

SELECT * FROM dg_order
WHERE date_xd_dr >= TO_DATE('2009-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND date_xd_dr <  TO_DATE('2009-11-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

優勢

• ✅ 精確包含全天所有秒數，無邊界遺漏
• ✅ 可使用索引範圍掃描 (INDEX RANGE SCAN)
• ✅ 不依賴 TRUNC 函數導致索引失效
• ✅ 邏輯清晰，易維護

對比：常見錯誤寫法

常用日期格式模型速查

進階日期運算技巧

-- 日期加減天數
SYSDATE + 7          -- 7 天后
SYSDATE - 30         -- 30 天前

-- 日期加減月份 (ADD_MONTHS 處理月末邊界)
ADD_MONTHS(SYSDATE, 3)    -- 3 個月後
ADD_MONTHS(SYSDATE, -1)   -- 1 個月前

-- 兩日期間隔天數 (含小數)
SYSDATE - TO_DATE('2009-01-01','YYYY-MM-DD')

-- 截斷到單位 (TRUNC)
TRUNC(SYSDATE, 'MM')   -- 本月 1 號 00:00:00
TRUNC(SYSDATE, 'YYYY') -- 今年 1 月 1 號 00:00:00

-- 遞增到下一個單位 (ROUND)
ROUND(SYSDATE, 'MM')   -- 下月 1 號 00:00:00 (若超過月中)

-- 最後一天
LAST_DAY(SYSDATE)      -- 本月最後一天 00:00:00

-- 下一個指定星期
NEXT_DAY(SYSDATE, 'FRIDAY')

NLS 參數影響與最佳實踐

• NLS_DATE_FORMAT 影響隱性轉換與顯示，生產代碼禁止依賴隱性轉換
• 永遠使用 TO_DATE('string', 'format') 顯式轉換
• Session 級設定：ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
• 應用層傳遞日期參數建議用 ISO 8601 格式：'2009-11-01T00:00:00' (Oracle 11g+ 支援 TO_TIMESTAMP_TZ)

時區與 TIMESTAMP 類型提示

• DATE：精確到秒，無時區資訊
• TIMESTAMP：精確到毫秒/微秒，無時區
• TIMESTAMP WITH TIME ZONE：含時區位移
• TIMESTAMP WITH LOCAL TIME ZONE：資料庫時區正規化儲存，查詢時轉換為 Session 時區
• 跨時區應用強烈建議使用 TIMESTAMP WITH LOCAL TIME ZONE

Oracle 日期操作的核心原則：顯式轉換、半開區間篩選、避免欄位加函數、善用 ADD_MONTHS/LAST_DAY 處理邊界。掌握這套模式，日期查詢既精確又高效。