DATEDIFF(interval, start, end):回傳兩日期間的間隔數 (整數)。DATEADD(interval, number, date):在日期上加減指定間隔,回傳新日期。關鍵概念:SQL Server 日期時間內部以 1900-01-01 00:00:00.000 為基準 (0 值)。所有計算本質上都是「算出與基準日的間隔數,再加回基準日」。
| 需求 | T-SQL 腳本 | 原理說明 |
|---|---|---|
| 本月第一天 | DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) | 算出與基準日相差月數,加回基準日 |
| 本週一 (週首) | DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) | 以週為間隔單位 |
| 今年第一天 | DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) | 以年為間隔單位 |
| 本季第一天 | DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) | 以季為間隔單位 |
| 今日午夜 (00:00:00.000) | DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) | 以日為間隔單位,去除時間部份 |
SQL Server datetime 精度為 3.33 毫秒,實際儲存單位為 3 毫秒 (0.000, 0.003, 0.007...)。故「一天的最後一刻」為 23:59:59.997。
| 需求 | T-SQL 腳本 |
|---|---|
| 上個月最後一天 | DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) |
| 去年最後一天 | DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) |
| 本月最後一天 | DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) |
| 今年最後一天 | DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) |
SELECT DATEADD(wk, DATEDIFF(wk, 0,
DATEADD(dd, 6 - DATEPART(day, GETDATE()), GETDATE())
), 0);
原理:先算出本月 6 號 (確保落在第一週),再套用「本週一」公式。
DECLARE @dt DATETIME = GETDATE();
SELECT DATEADD(day, DATEDIFF(day, 0, @dt), 0); -- 2024-01-15 00:00:00.000
SELECT DATENAME(weekday, GETDATE()); -- 如 'Monday'
-- 方法 A:下月 1 號減去本月 1 號
DECLARE @m INT = 2; -- 2 月
SELECT DATEDIFF(day,
CAST('2024-' + CAST(@m AS VARCHAR) + '-01' AS DATE),
CAST('2024-' + CAST(@m+1 AS VARCHAR) + '-01' AS DATE)
);
-- 方法 B:用本月最後一天的 DAY()
SELECT DAY(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)));
-- 方法 A:看 2 月最後一天
SELECT CASE DAY(DATEADD(ms, -3, DATEADD(mm, 2, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))))
WHEN 28 THEN '平年' ELSE '閏年' END;
-- 方法 B:DATEDIFF 計算 2 月天數
SELECT CASE DATEDIFF(day,
CAST(DATENAME(year, GETDATE()) + '-02-01' AS DATE),
DATEADD(mm, 1, CAST(DATENAME(year, GETDATE()) + '-02-01' AS DATE))
) WHEN 28 THEN '平年' ELSE '閏年' END;
DECLARE @m TINYINT = MONTH(GETDATE()), @start SMALLDATETIME;
SELECT @m = CASE
WHEN @m BETWEEN 1 AND 3 THEN 1
WHEN @m BETWEEN 4 AND 6 THEN 4
WHEN @m BETWEEN 7 AND 9 THEN 7
ELSE 10 END;
SET @start = CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(@m AS VARCHAR) + '-01';
SELECT DATEDIFF(day, @start, DATEADD(mm, 3, @start));
SQL Server 2016+ 引入 DATETRUNC (2022 正式支援)、DATE_BUCKET 等新函數,可大幅簡化上述寫法。新專案建議優先使用新式函數。
「間隔思維」是掌握 DATEADD/DATEDIFF 的關鍵:先算間隔數,再加減基準日。熟記上表常用模板,即能應對 90% 日期邊界計算需求。