SQL Server 日期運算核心技巧:**以「基準日 1900-01-01」為錨點,用 DATEDIFF 算間隔數,再 DATEADD 加回去**,可精準取得各類邊界日期時間,避免字串拼接的格式風險。
DATEADD(間隔單位, DATEDIFF(間隔單位, 0, 參考日期), 0)
0 = 基準日 1900-01-01 00:00:00.000DATEDIFF 算出間隔數 (天/週/月/季/年)DATEADD 加回基準日 → 該間隔的**起始瞬間**| 需求 | SQL 寫法 | 結果範例 (假設今天 2024-03-15) |
|---|---|---|
| 本月第一天 00:00 | DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) | 2024-03-01 00:00:00.000 |
| 本月最後一天 23:59:59.997 | DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)) | 2024-03-31 23:59:59.997 |
| 上個月最後一天 | DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) | 2024-02-29 23:59:59.997 |
| 本月第一個週一 | DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6-DATEPART(DAY, GETDATE()), GETDATE())), 0) | 2024-03-04 00:00:00.000 |
| 本年第一天 | DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) | 2024-01-01 00:00:00.000 |
| 本年最後一天 | DATEADD(MS, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())+1, 0)) | 2024-12-31 23:59:59.997 |
| 去年最後一天 | DATEADD(MS, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) | 2023-12-31 23:59:59.997 |
| 本季度第一天 | DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) | 2024-01-01 00:00:00.000 |
| 本週週一 | DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) | 2024-03-11 00:00:00.000 |
| 今天零點 (半夜) | DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) | 2024-03-15 00:00:00.000 |
-- 本月資料
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND OrderDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0);
-- 本週資料 (週一起算)
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)
AND OrderDate < DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE())+1, 0);
-- 本季資料
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
AND OrderDate < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE())+1, 0);
關鍵技巧:用 **< 下一個區間起始** 替代 **<= 當前區間結束**,避免毫秒精度漏資料。
| 函數 | 用途 | 範例 |
|---|---|---|
| GETDATE() | 當前日期時間 | 2024-03-15 14:30:25.123 |
| DATEADD(interval, n, dt) | 日期加減 | DATEADD(DAY, 2, '2024-03-15') → 2024-03-17 |
| DATEDIFF(interval, dt1, dt2) | 間隔差值 | DATEDIFF(DAY, '2024-03-01', '2024-03-15') → 14 |
| DATEPART(interval, dt) | 取整數部分 | DATEPART(MONTH, GETDATE()) → 3 |
| DATENAME(interval, dt) | 取名稱字串 | DATENAME(WEEKDAY, GETDATE()) → Friday |
| DAY()/MONTH()/YEAR() | 簡寫取值 | MONTH(GETDATE()) → 3 |
year, yy, yyyy -- 年
quarter, qq, q -- 季
month, mm, m -- 月
dayofyear, dy, y -- 年第幾天
day, dd, d -- 日
week, wk, ww -- 週
weekday, dw, w -- 週幾
hour, hh -- 時
minute, mi, n -- 分
second, ss, s -- 秒
millisecond, ms -- 毫秒
週邊界計算受 SET DATEFIRST 影響 (預設 7=週日)。若環境不同,需調整 6-DATEPART(DAY,...) 中的常數。