Oracle 日期處理全攻略：格式轉換、語言設置、空值、範圍陷阱、衝突解決

日期是業務系統中最容易踩坑的類型。本文匯總 TO_CHAR/TO_DATE 格式模板、會話/語句級語言設置、NULL 處理、BETWEEN 範圍陷阱、字符集衝突解決，構建日期處理最佳實踐清單。

一、秒數轉天/時/分/秒顯示

SELECT TO_CHAR(FLOOR(936000/3600/24)) || '天' ||
       TO_CHAR(MOD(FLOOR(936000/3600), 24)) || '小時'
FROM dual;  -- 10天10小時

二、TO_DATE 格式元素速查

24小時制：00:00:00 - 23:59:59；12小時制：01:00:00 - 12:59:59

三、日期/字符雙向轉換實戰

-- 1. 數字轉拼寫日期
SELECT TO_CHAR(TO_DATE(222, 'J'), 'JSP') FROM dual;  -- TWO HUNDRED TWENTY-TWO

-- 2. 求某天星期
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'), 'DAY') FROM dual;  -- 星期一

-- 3. 指定語言輸出星期
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'), 'DAY',
       'NLS_DATE_LANGUAGE=AMERICAN') FROM dual;  -- MONDAY

-- 4. 會話級語言設置
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

-- 5. 語句級語言設置（TO_DATE/TO_CHAR 第三參數）
TO_DATE('2002-08-26', 'YYYY-MM-DD', 'NLS_DATE_LANGUAGE=AMERICAN')

四、日期運算與空值處理

-- 1. 兩日期天數差
SELECT FLOOR(SYSDATE - TO_DATE('20020405','YYYYMMDD')) FROM dual;

-- 2. 日期為 NULL 的 UNION 技巧
SELECT id, active_date FROM table1
UNION
SELECT 1, TO_DATE(NULL) FROM dual;  -- 必須 TO_DATE(NULL)，不能直接 NULL

-- 3. BETWEEN 範圍陷阱
WHERE a_date BETWEEN TO_DATE('20011201','YYYYMMDD')
                 AND TO_DATE('20011231','YYYYMMDD')
-- 若 a_date 有時間分量，12月31日 12:00:00 之後及 12月1日 12:00:00 之前會被排除
-- 解決：WHERE a_date >= TO_DATE('20011201','YYYYMMDD')
--        AND a_date <  TO_DATE('20020101','YYYYMMDD')
--    或：WHERE TRUNC(a_date) BETWEEN ...

五、字符集衝突與 NLS 參數查看

-- 1. 安裝字符集決定默認日期格式
-- US7ASCII → '01-JAN-01'
-- AL32UTF8 → 依 NLS_TERRITORY

-- 2. 系統/會話級修正
ALTER SYSTEM SET NLS_DATE_LANGUAGE = 'AMERICAN';  -- 需重啟
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN'; -- 即時生效

-- 3. 查看所有 NLS 參數
SELECT * FROM nls_session_parameters;   -- 當前會話
SELECT * FROM v$nls_parameters;         -- 實例級

六、最佳實踐清單

1. 應用層統一使用 java.time.LocalDateTime / DATE，避免字符串拼接 SQL。
2. 數據庫端：表列類型用 DATE 或 TIMESTAMP，不存字符串。
3. 範圍查詢統一用 >= 起始 AND < 結束+1天，避免 BETWEEN 時間分量陷阱。
4. 跨語言環境部署時，啟動腳本強制 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8。
5. 定期巡檢 NLS_SESSION_PARAMETERS 確保一致性。