報表、排程、帳務結算常需「本月第一天」、「上月最後一天」等邊界值。SQL Server 無內建 EOMONTH(2012+ 才有),利用 DATEADD/DATEDIFF 組合可通用於所有版本。
| 需求 | T-SQL 運算式 | 說明 |
|---|---|---|
| 本月第一天 00:00 | DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) | 以 1900-01-01 為基準累算月差 |
| 本月最後一天 23:59:59.997 | DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)) | 下月第一天減 3 毫秒(DATETIME 精度) |
| 上月最後一天 | DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) | 本月第一天減 3 毫秒 |
| 本年第一天 | DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) | |
| 本年最後一天 | DATEADD(MS, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())+1, 0)) | |
| 上年最後一天 | DATEADD(MS, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) | |
| 本季第一天 | DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) | |
| 本週一 (週一為首日) | DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) | 1900-01-01 為週一 |
| 本月第一個週一 | DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6-DATEPART(DAY, GETDATE()), GETDATE())), 0) |
-- 本月資料
DECLARE @Start DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
DECLARE @End DATETIME = DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0));
SELECT * FROM Orders WHERE OrderDate >= @Start AND OrderDate <= @End;
-- 本週資料(週一起算)
DECLARE @WeekStart DATETIME = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0);
DECLARE @WeekEnd DATETIME = DATEADD(MS, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE())+1, 0));
SELECT * FROM Orders WHERE OrderDate >= @WeekStart AND OrderDate < @WeekEnd;
-- 本季資料
DECLARE @QStart DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);
DECLARE @QEnd DATETIME = DATEADD(MS, -3, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE())+1, 0));
SELECT * FROM Orders WHERE OrderDate >= @QStart AND OrderDate < @QEnd;
關鍵:使用 >= @Start AND < @NextStart(或 <= @End),避免 DATEPART/MONTH/YEAR 導致掃描全表。
-- 當月總天數
SELECT DATEDIFF(DAY, @Start, DATEADD(MONTH, 1, @Start));
-- 當前星期幾名稱
SELECT DATENAME(WEEKDAY, GETDATE()); -- 如 'Thursday'
-- 月初
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
-- 月末
SELECT EOMONTH(GETDATE());
-- 指定月份月末
SELECT EOMONTH(GETDATE(), -1); -- 上月
掌握邊界計算,報表與排程不再硬編碼日期常數,自動適應月份長短與閏年。