Oracle 日期時間操作實用速查：BETWEEN、ADD_MONTHS、TO_CHAR、日期差、INTERSECT

整理 Oracle 日期時間最常用的 5 大操作模式：區間查詢、更新時間戳、日期差計算、加減月份轉字串、SUBSTR 字串截取，附 SQL Server 對照與實用技巧。

1. 日期區間查詢（BETWEEN / 比較運算子）

SELECT * FROM shengjibiao t
WHERE t.createtime BETWEEN TO_DATE('2009-07-26','YYYY-MM-DD')
                       AND TO_DATE('2009-07-29','YYYY-MM-DD');

-- 等價寫法（含邊界）
SELECT * FROM shengjibiao t
WHERE t.createtime >= TO_DATE('2009-07-26','YYYY-MM-DD')
  AND t.createtime <  TO_DATE('2009-07-30','YYYY-MM-DD');  -- 避免時分秒邊界問題

2. 更新 TIMESTAMP 欄位（含時分秒）

UPDATE shengjilogin t
SET t.starttime = TO_DATE('2009-01-02 01:02:01','YYYY-MM-DD HH24:MI:SS')
WHERE t.username = '220819';

3. 兩日期時間差（天/秒）

-- 相減得天數（含小數）
SELECT TO_DATE('2009-04-01 13:02:01','YYYY-MM-DD HH24:MI:SS')
     - TO_DATE('2009-03-08 13:02:01','YYYY-MM-DD HH24:MI:SS')
FROM dual;  -- 結果: 24.000... 天

-- 換算秒數
SELECT (d1 - d2) * 24 * 60 * 60 AS diff_seconds FROM dual;

4. 加減月份並轉字串輸出

-- 回傳 DATE 型別
SELECT ADD_MONTHS(TO_DATE('2009-07-27 02:31:11','YYYY-MM-DD HH24:MI:SS'), 2) FROM dual;

-- 回傳格式化字串（報表常用）
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2009-07-27 02:31:11','YYYY-MM-DD HH24:MI:SS'), 2),
               'YYYY-MM-DD HH24:MI:SS') AS terTime FROM dual;

5. SUBSTR 字串截取（Oracle 位置從 1 起，支援負數從右）

SELECT SUBSTR('chenyp', 2, 4) FROM dual;  -- 'heny'
SELECT SUBSTR('HelloWorld', -5, 5) FROM dual;  -- 'World' (從右數第 5 字元起取 5 個)

進階：INTERSECT 取交集（多查詢結果去重交集）

SELECT empno FROM emp WHERE deptno = 10
INTERSECT
SELECT empno FROM emp WHERE sal > 2000;

Oracle vs SQL Server 日期函數對照

實用技巧：主外鍵理解修正

主鍵（PK）是**唯一識別實體**的屬性組合；外鍵（FK）是**引用另一實體 PK** 的屬性，建立實體間關聯。PK/FK 不是為了「連接查詢服務」，而是為了**實體完整性與參考完整性**。連接查詢只是利用這種關聯的副產物。理解本質，設計才不會偏離正規化原則。

日期時間處理的核心：**統一用 DATE/TIMESTAMP 型別存儲、用 TO_CHAR/TO_DATE 格式化、用 ADD_MONTHS/數學運算計算、用索引支援區間查詢**。拒絕字串存日期。