Oracle 產生當年日曆表的 SQL 技巧

在開發報表或統計功能時，常需要一份完整的當年日曆表作為基礎資料。Oracle 利用 CONNECT BY LEVEL 搭配日期運算，即可輕鬆產生從 1 月 1 日到 12 月 31 日的完整日期序列，無需建立實體資料表。

核心 SQL 語法

SELECT TO_DATE((SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL) || '0101', 'YYYYMMDD') + LEVEL - 1 AS dayofyear
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(TO_DATE((SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL) || '1231', 'YYYYMMDD'), 'DDD');

語法解析

• 取得當年首日：TO_CHAR(SYSDATE, 'YYYY') || '0101' 組成「YYYY0101」字串，再轉為 DATE 型別。
• 產生序列：+ LEVEL - 1 從 0 開始遞增，配合 CONNECT BY LEVEL 產生多筆資料列。
• 控制筆數：TO_CHAR(..., 'DDD') 取得該年 12 月 31 日是當年的第幾天（平年 365、閏年 366），作為 LEVEL 的上限。

執行結果範例

查詢將回傳類似下圖的日期清單，可直接套用於左連接、缺漏日期補齊等場景：

進階應用提示

• 若需包含時分秒，可在外層包裝 TO_CHAR(dayofyear, 'YYYY-MM-DD HH24:MI:SS')。
• 搭配 WITH 子查詢（CTE）可進一步加入星期、季別、是否假日等衍生欄位，構建完整的時間維度表。