SQL Server 日期時間計算大全：相對日期、月首末、週首末、季度、年度、閏年、周序數完整函式庫

核心函式：DATEADD / DATEDIFF / DATEPART / DATENAME

所有日期運算建基於 1900-01-01 (Day 0) 為基準點。掌握「間差 + 基準」模式即可推導所有相對日期。

通用模板：任意時間單位的「當期首/末」、「上期首/末」、「下期首/末」

-- 當期首日 (精確到日，時分秒歸零)
DATEADD(, DATEDIFF(, 0, @Date), 0)

-- 當期首日 (保留原時分秒)
DATEADD(DAY, 1 - DATEPART(DAY, @Date), @Date)  -- 月首日保留時間

-- 當期末日
DATEADD(, 1 + DATEDIFF(, 0, @Date), 0) - 1  -- 通用：下期首日 - 1天
DATEADD(DAY, -1, DATEADD(, 1 + DATEDIFF(, 0, @Date), 0))  -- 等價

-- 上期首日
DATEADD(, -1 + DATEDIFF(, 0, @Date), 0)
DATEADD(, DATEDIFF(, 0, @Date) - 1, 0)

-- 下期首日
DATEADD(, 1 + DATEDIFF(, 0, @Date), 0)

完整函式庫速查表

進階計算

本月天數

DAY(DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1))  -- 利用上年末日 + 2月 = 本年2月末日
DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0), DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, @Date), 0))

本季天數 / 本年天數

DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @Date), 0), DATEADD(QUARTER, 1+DATEDIFF(QUARTER, 0, @Date), 0))
DATEDIFF(DAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0), DATEADD(YEAR, 1+DATEDIFF(YEAR, 0, @Date), 0))

判斷閏年

-- 方法 1：本年天數
CASE DATEDIFF(DAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0), DATEADD(YEAR, 1+DATEDIFF(YEAR, 0, @Date), 0))
    WHEN 365 THEN '平年' ELSE '閏年' END

-- 方法 2：2月天數
CASE DAY(DATEADD(MONTH, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), -1)))
    WHEN 28 THEN '平年' ELSE '閏年' END

當年第 N 天 / 週 / 月 / 季

DATEPART(DAYOFYEAR, @Date)   -- 第 N 天
DATEPART(WEEK, @Date)        -- 第 N 週 (依 DATEFIRST)
DATEPART(MONTH, @Date)       -- 第 N 月
DATEPART(QUARTER, @Date)     -- 第 N 季

本月第 N 個週一/週二...

-- 本月第一個週一 (1900-01-01 為週一)
DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6-DATEPART(DAY, @Date), @Date)), 0)
-- 第 2 個週一：+7，第 1 個週二：+1，以此類推

最佳實踐建議

1. 統一用 0/-1 基準點：0 = 1900-01-01 (週一)、-1 = 1899-12-31 (週日)，避免字串日期格式問題
2. 避免字串運算：CONVERT(VARCHAR, @Date, 112) 等字串操作效能差、易錯
3. 週相關計算：明確宣告 SET DATEFIRST 7 (美式週日首) 或 1 (歐式週一首)，或用基準點法 (0/-1) 免受影響
4. 封裝為內嵌函數：CREATE FUNCTION dbo.FirstDayOfMonth(@Date DATE) RETURNS DATE AS BEGIN RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) END，查詢中直接呼叫
5. 索引友善查詢：WHERE OrderDate >= dbo.FirstDayOfMonth(GETDATE()) AND OrderDate < dbo.FirstDayOfMonth(DATEADD(MONTH,1,GETDATE())) 可用索引

版本差異提醒

• SQL 2008+：DATE, TIME, DATETIME2, DATETIMEOFFSET 新型別，精度更高
• SQL 2012+：EOMONTH(@Date) 直接取月末日、DATEFROMPARTS 組合日期
• 舊版 DATETIME 精度 3.33ms，新版 DATETIME2 100ns