Oracle 日期時間函數實戰：常用 SQL 範例大全

前言

Oracle 日期運算是開發與管理常見需求。本文彙整 16 個實用範例，涵蓋週期計算、格式轉換、邊界取值、閏年判斷、季度計算等，供快速查閱與套用。

範例彙總

1. 取得當前日期為本月第幾週

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD W HH24:MI:SS') FROM DUAL;
-- 結果範例: 20030327 4 18:16:09
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL; -- 單獨取週數

2. 取得當前日期為一週中第幾天 (週日=1)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'D') FROM DUAL;

常用格式模板對照：

3. 中文顯示星期幾

SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 例: 星期四

4. 日期欄位有索引時的查詢優化

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

設定 Session 層級日期格式，避免隱含轉換導致索引失效。

5-7. 當前/今日起迄/明日零點

-- 當前日期時間
SELECT SYSDATE FROM DUAL;
-- 今日 00:00:00
SELECT TRUNC(SYSDATE) FROM DUAL;
-- 今日最後一秒 (近似)
SELECT TRUNC(SYSDATE) + 0.99999 FROM DUAL;
-- 加 N 小時
SELECT TRUNC(SYSDATE) + 1/24 FROM DUAL;  -- 1小時後
SELECT TRUNC(SYSDATE) + 7/24 FROM DUAL;  -- 7小時後
-- 明日 00:00:00
SELECT TRUNC(SYSDATE + 1) FROM DUAL;
SELECT TRUNC(SYSDATE) + 1 FROM DUAL;

8-10. 月首、月尾、下月首日

-- 本月 1 號 00:00:00
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
-- 下月 1 號
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') FROM DUAL;
-- 本月最後一天 (多種寫法)
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(TRUNC(SYSDATE)) FROM DUAL;
SELECT TRUNC(LAST_DAY(SYSDATE)) FROM DUAL;
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - 1 FROM DUAL;

11. 列出今年每一天

SELECT TRUNC(SYSDATE, 'YYYY') + ROWNUM - 1 AS DATE0
FROM (SELECT ROWNUM RN FROM ALL_OBJECTS WHERE ROWNUM < 366);

12. 今日為年中第幾天

SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

13. 日期加減年份

SELECT ADD_MONTHS(SYSDATE, 24) FROM DUAL; -- 加 2 年

14-15. 判斷今年/兩年後是否閏年

-- 今年
SELECT DECODE(TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'Y') + 31), 'DD'), '29', '閏年', '平年') FROM DUAL;
-- 兩年後
SELECT DECODE(TO_CHAR(LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE, 24), 'Y') + 31), 'DD'), '29', '閏年', '平年') FROM DUAL;

原理：取該年 1 月 31 日，加 31 天到 3 月 2 日，再取該月最後一天，若為 29 號則為閏年。

16. 取得季度

SELECT CEIL(TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) / 3) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; -- 內建格式更簡潔