萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 日期時間邊界計算大全:本月/上月/本年/本季/本週首尾日與區間查詢技巧

報表、排程、帳務結算常需「本月第一天」、「上月最後一天」等邊界值。SQL Server 無內建 EOMONTH(2012+ 才有),利用 DATEADD/DATEDIFF 組合可通用於所有版本。

核心邊界函數速查

需求T-SQL 運算式說明
本月第一天 00:00DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)以 1900-01-01 為基準累算月差
本月最後一天 23:59:59.997DATEADD(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)

區間查詢模式(SARGable,可用索引)

-- 本月資料
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'

SQL Server 2012+ 簡化寫法

-- 月初
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
-- 月末
SELECT EOMONTH(GETDATE());
-- 指定月份月末
SELECT EOMONTH(GETDATE(), -1); -- 上月

掌握邊界計算,報表與排程不再硬編碼日期常數,自動適應月份長短與閏年。

copyright © 萬盛學電腦網 all rights reserved