萬盛學電腦網

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

SQL Server 日期計算大全:DATEADD 與 DATEDIFF 實戰技巧

核心函數回顧

  • 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)以日為間隔單位,去除時間部份

「最後一天」計算技巧:減去 3 毫秒

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 號 (確保落在第一週),再套用「本週一」公式。

附錄:其他實用日期處理

1. 移除時間部份 (僅保留日期)


DECLARE @dt DATETIME = GETDATE();
SELECT DATEADD(day, DATEDIFF(day, 0, @dt), 0);  -- 2024-01-15 00:00:00.000

2. 取得星期幾名稱


SELECT DATENAME(weekday, GETDATE());  -- 如 'Monday'

3. 取得某月天數


-- 方法 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)));

4. 判斷閏年


-- 方法 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;

5. 當季天數


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% 日期邊界計算需求。

copyright © 萬盛學電腦網 all rights reserved