Oracle 取得本週第一天與最後一天日期的 SQL 技巧

需求背景

報表開發、資料統計常需按「週」切分資料。Oracle 無內建 WEEK_START 函數，但可善用 TRUNC 與 TO_CHAR 組合快速計算。

核心邏輯

TRUNC(sysdate, 'DD') 取得今日零點；TO_CHAR(sysdate, 'D') 回傳星期幾（1=週日 ... 7=週六，視 NLS_TERRITORY 而定）。以 **週一為週首** 為例：

本週第一天（週一）

SELECT TRUNC(SYSDATE, 'DD') - TO_CHAR(SYSDATE, 'D') + 2 AS week_start
FROM dual;

本週最後一天（週日）

SELECT TRUNC(SYSDATE, 'DD') - TO_CHAR(SYSDATE, 'D') + 8 AS week_end
FROM dual;

參數調整說明

• 若環境 NLS_TERRITORY=AMERICA（週日為週首），週一公式改為 +1，週日為 +7。
• 建議在查詢中顯式設定：TO_CHAR(sysdate, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') 避免環境差異導致錯誤。

進階應用：產生任意日期所在週區間

WITH params AS (
    SELECT DATE '2024-03-15' AS target_date FROM dual
)
SELECT 
    target_date,
    TRUNC(target_date, 'DD') - TO_CHAR(target_date, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') + 2 AS week_monday,
    TRUNC(target_date, 'DD') - TO_CHAR(target_date, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') + 8 AS week_sunday
FROM params;

最佳實踐建議

1. 封裝為 FUNCTION get_week_start(p_date DATE) RETURN DATE 供重複呼叫。
2. 資料倉庫維度表預先計算 WEEK_START_DT, WEEK_END_DT，查詢時直接 JOIN 避免重複運算。
3. 注意跨年週邊界（ISO 8601 定義），必要時改用 TRUNC(date, 'IW')（ISO 週一為週首）。