SQL Server DATEDIFF() 函數 (計算兩個日期之間的時間間隔)

DATEDIFF() 是 SQL Server 中用來計算兩個日期之間時間間隔的函數。它可以返回指定時間單位（年、月、日、小時、分鐘、秒等）的差值。

DATEDIFF() 語法 (Syntax)

DATEDIFF(datepart, startdate, enddate)

• datepart：返回值的時間單位（見下方表格）。
• startdate：開始日期（較早的日期）。
• enddate：結束日期（較晚的日期）。
• 返回值：整數，表示 enddate - startdate 的差值（以指定的 datepart 為單位）。

datepart 可用的值

DATEDIFF() 函數用法範例 (Example)

計算相差天數

SELECT DATEDIFF(day, '2024-01-01', '2024-12-31');
-- 結果：365（2024 年是閏年）

SELECT DATEDIFF(day, '2024-11-22', '2024-11-29');
-- 結果：7

計算相差月數

SELECT DATEDIFF(month, '2024-01-01', '2024-06-01');
-- 結果：5

SELECT DATEDIFF(month, '2024-01-31', '2024-02-01');
-- 結果：1（即使只差 1 天，但跨月了）

計算相差年數

SELECT DATEDIFF(year, '2020-12-31', '2024-01-01');
-- 結果：4（注意：這是年份的差異，不是完整年數）

SELECT DATEDIFF(year, '2020-01-01', '2024-12-31');
-- 結果：4

計算相差小時數

SELECT DATEDIFF(hour, '2024-11-22 08:00:00', '2024-11-22 17:30:00');
-- 結果：9

SELECT DATEDIFF(hour, '2024-11-22 00:00:00', '2024-11-23 00:00:00');
-- 結果：24

計算相差分鐘數

SELECT DATEDIFF(minute, '2024-11-22 10:00:00', '2024-11-22 10:45:30');
-- 結果：45

SELECT DATEDIFF(minute, '2024-01-01', '2024-01-02');
-- 結果：1440（24 小時 × 60 分鐘）

計算相差秒數

SELECT DATEDIFF(second, '2024-11-22 10:00:00', '2024-11-22 10:01:30');
-- 結果：90

負數結果

當 startdate 晚於 enddate 時，返回負數：

SELECT DATEDIFF(day, '2024-12-31', '2024-01-01');
-- 結果：-365

實際應用範例

計算訂單處理天數

SELECT 
    order_id,
    order_date,
    shipped_date,
    DATEDIFF(day, order_date, shipped_date) AS days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL;

計算用戶年齡

-- 注意：這個計算方式不夠精確，因為它只計算年份差異
SELECT 
    user_id,
    birthday,
    DATEDIFF(year, birthday, GETDATE()) AS age_approximate
FROM users;

-- 更精確的年齡計算
SELECT 
    user_id,
    birthday,
    DATEDIFF(year, birthday, GETDATE()) - 
    CASE WHEN DATEADD(year, DATEDIFF(year, birthday, GETDATE()), birthday) > GETDATE() 
         THEN 1 ELSE 0 END AS age
FROM users;

計算服務時長

SELECT 
    employee_id,
    hire_date,
    DATEDIFF(year, hire_date, GETDATE()) AS years_of_service,
    DATEDIFF(month, hire_date, GETDATE()) AS months_of_service,
    DATEDIFF(day, hire_date, GETDATE()) AS days_of_service
FROM employees;

篩選特定期間內的記錄

-- 取得最近 30 天內的訂單
SELECT * FROM orders
WHERE DATEDIFF(day, order_date, GETDATE()) <= 30;

-- 取得最近 1 小時內的活動
SELECT * FROM activity_logs
WHERE DATEDIFF(hour, created_at, GETDATE()) <= 1;

進階應用：取得特定日期

結合 DATEADD() 和 DATEDIFF() 可以計算各種特定日期：

取得本年第一天

SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) AS FirstDayOfYear;
-- 結果：2024-01-01 00:00:00.000

取得本月第一天

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS FirstDayOfMonth;
-- 結果：2024-11-01 00:00:00.000

取得本季第一天

SELECT DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0) AS FirstDayOfQuarter;
-- 結果：2024-10-01 00:00:00.000（第四季從 10 月開始）

取得本週第一天

SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) AS FirstDayOfWeek;
-- 結果：2024-11-18 00:00:00.000（週一）

取得本月最後一天

-- 方法 1：使用 EOMONTH（SQL Server 2012+）
SELECT EOMONTH(GETDATE()) AS LastDayOfMonth;

-- 方法 2：計算下個月第一天再減 1 天
SELECT DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)) AS LastDayOfMonth;

-- 方法 3：減去 3 毫秒（考慮 datetime 精度）
SELECT DATEADD(millisecond, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)) AS LastMomentOfMonth;

DATEDIFF() vs DATEDIFF_BIG()

對於非常大的時間差（例如以毫秒計算跨越數百年的差異），DATEDIFF() 可能會溢位。SQL Server 2016 引入了 DATEDIFF_BIG() 函數，返回 bigint 型別：

-- DATEDIFF 可能溢位
SELECT DATEDIFF(second, '1900-01-01', '2100-12-31');
-- 可能產生錯誤

-- DATEDIFF_BIG 不會溢位
SELECT DATEDIFF_BIG(second, '1900-01-01', '2100-12-31');
-- 結果：6341126399

注意事項

• DATEDIFF() 計算的是「邊界跨越」的次數，而不是完整的時間間隔。例如 DATEDIFF(month, '2024-01-31', '2024-02-01') 返回 1，即使只差 1 天。
• 對於精確的年齡或時間差計算，可能需要額外的邏輯來處理邊界情況。
• 如果任一參數為 NULL，結果也是 NULL。

更多 SQL Server 相關的日期時間函數在這邊
更多 MySQL 相關的日期時間函數在這邊