SQL Server 日期時間運算完全手冊：從「前一天」到「季度末」的 50+ 實用模式

日期時間運算是 T-SQL 開發的高頻痛點。本文系統整理 SQL Server 中最實用的 50+ 日期運算模式，涵蓋日、週、月、季、年各層級的首尾邊界計算、偏移運算、閏年判斷、年週序數等，所有語句均經驗證可直接生產環境使用。

核心函數速查

• GETDATE()：當前日期時間
• DATEADD(interval, number, date)：日期加減
• DATEDIFF(interval, start, end)：日期差值
• DATEPART(interval, date)：取整數部分（年、月、日、週等）
• DATENAME(interval, date)：取字串部分（月份名、星期名）
• DAY/ MONTH/ YEAR()：DATEPART 同義詞

基準日期技巧：0 = '1900-01-01'

SQL Server 內部以 1900-01-01 為基準日（整數 0）。利用此特性可簡化首尾日計算：

-- 本月第一天 00:00:00.000
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

-- 本月第一天 保留時分秒
SELECT DATEADD(DAY, 1 - DATEPART(DAY, GETDATE()), GETDATE())

-- 本月最後一天 23:59:59.997
SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), 0))
-- 或
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)  -- -1 = '1899-12-31'

完整模式庫

月份運算

週運算（以週日為週首）

-- 設定週日為第一天（避免 @@DATEFIRST 干擾）
SET DATEFIRST 7;

-- 本週週日
SELECT DATEADD(WEEKDAY, 1-DATEPART(WEEKDAY, @d), @d)
-- 不受語言影響寫法（1989-12-31 是週日 = -1）
SELECT DATEADD(WEEK, DATEDIFF(WEEK, -1, @d), -1)

-- 本週週一
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @d), 0)  -- 1900-01-01 是週一 = 0

-- 上週週日
SELECT DATEADD(WEEK, -1+DATEDIFF(WEEK, -1, @d), -1)
-- 下週週日
SELECT DATEADD(WEEK, 1+DATEDIFF(WEEK, -1, @d), -1)

年、季運算

進階計算

-- 本月天數
SELECT DATEDIFF(DAY, 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @d), 0),
    DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, @d), 0))

-- 判斷閏年
SELECT CASE 
    WHEN DATEDIFF(DAY, DATEADD(YEAR, DATEDIFF(YEAR,0,@d),0), DATEADD(YEAR,1+DATEDIFF(YEAR,0,@d),0)) = 366 
    THEN '閏年' ELSE '平年' END

-- 今年第幾天
SELECT DATEPART(DAYOFYEAR, @d)

-- 今年第幾週
SELECT DATEPART(WEEK, @d)

-- 今月第幾週（以週日為首）
SELECT DATEPART(WEEK, @d) - DATEPART(WEEK, DATEADD(MONTH, DATEDIFF(MONTH,0,@d),0)) + 1

-- 本月第一個週一
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6-DATEPART(DAY,@d), @d)), 0)

效能與最佳實務建議

• 避免在 WHERE 條件對欄位做運算： WHERE DATEADD(DAY, 1, Col) = @d 會導致索引失效，改寫為 WHERE Col = DATEADD(DAY, -1, @d)。
• 統一使用基準日 0/-1 技巧： 可讀性高、不受語言設定影響、執行計畫穩定。
• 大量資料篩選： 建立計算欄位或索引檢視表物化常用邊界值（如月首日），配合範圍查詢 Col >= @MonthStart AND Col < @NextMonthStart。
• 跨時區系統： 統一儲存 UTC（GETUTCDATE()），顯示層再轉換。

掌握這些模式，即可輕鬆應對報表區間、帳期結算、排程觸發等 90% 以上的日期運算需求。