萬盛學電腦網

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

SQL Server 日期時間函數實戰手冊:GETDATE、DATEDIFF、DATEPART、CONVERT 核心用法

從當前時間到複雜業務邏輯的日期運算全覆蓋

日期時間處理是業務邏輯高發區。本文聚焦 **GETDATE、DATEDIFF、DATEPART、DATENAME、CONVERT** 五大核心函數,給出實戰範例與避坑指南。

核心函數速查

函數用途關鍵參數 interval 值
GETDATE()當前系統日期時間 (datetime)—
SYSDATETIME()高精度當前時間 (datetime2, 100ns)—
DATEADD(interval, n, date)日期加減year/yy/qq/quarter, month/mm, day/dd/dy, week/wk/ww, hour/hh, minute/mi/n, second/ss/s, millisecond/ms
DATEDIFF(interval, start, end)日期差值 (有號整數)同上,回傳跨越的邊界數
DATEPART(interval, date)提取部分 (INT)year/yy, quarter/qq, month/mm, dayofyear/dy, day/dd, week/wk, weekday/dw, hour/hh, minute/mi, second/ss, millisecond/ms
DATENAME(interval, date)提取部分 (NVARCHAR)同上,月份/星期名稱本地化
CONVERT(target_type, date, style)格式化/類型轉換style 代碼見前文

實戰範例集

1. 相對時間篩選 (SARGable 寫法,走索引)

-- 最近 7 天
WHERE CreatedAt >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) 
  AND CreatedAt < CAST(GETDATE() AS DATE);

-- 本月迄今
WHERE CreatedAt >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND CreatedAt < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

-- 上個月完整範圍
WHERE CreatedAt >= DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND CreatedAt < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

2. 業務週期計算

-- 兩日期相差天數
SELECT DATEDIFF(DAY, '2005-07-20', '2005-07-25 22:56:32'); -- 5 (跨越 5 個午夜)

-- 精確秒數
SELECT DATEDIFF(SECOND, '2005-07-20', '2005-07-25 22:56:32'); -- 514592

-- 年齡計算 (精確到生日)
SELECT DATEDIFF(YEAR, BirthDate, GETDATE()) - 
       CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, BirthDate, GETDATE()), BirthDate) > GETDATE() THEN 1 ELSE 0 END;

3. 日期部分提取與分組統計

-- 當前是星期幾 (1=週日...7=週六)
SELECT DATEPART(WEEKDAY, GETDATE()); -- 或 @@DATEFIRST 影響

-- 本年第幾週 (ISO 8601 週需複雜邏輯)
SELECT DATEPART(WEEK, GETDATE());

-- 按月份分組統計
SELECT DATEPART(YEAR, OrderDate) AS Yr, DATEPART(MONTH, OrderDate) AS Mo, COUNT(*) AS Cnt
FROM Orders
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)
ORDER BY Yr, Mo;

4. 格式化輸出 (前端直用)

-- 日期
SELECT CONVERT(VARCHAR(10), GETDATE(), 120); -- 2006-05-16
-- 時間
SELECT CONVERT(VARCHAR(8), GETDATE(), 108);  -- 10:57:49
-- 完整
SELECT CONVERT(VARCHAR(19), GETDATE(), 120); -- 2006-05-16 10:57:49

避坑指南

  • ❌ `WHERE YEAR(DateCol) = 2023` → ✅ `WHERE DateCol >= '20230101' AND DateCol < '20240101'`
  • ❌ `DATEDIFF(DAY, Start, End)` 計算住宿天數 (入住日算、退房日不算) → 需視業務調整 `+1/-1`
  • ⚠ `DATEPART(WEEK, ...)` 依賴 `SET DATEFIRST`,跨環境不一致。ISO 週需自定義函數。
  • ⚠ `GETDATE()` 精度 ~3ms,高精度場景用 `SYSDATETIME()` / `SYSUTCDATETIME()`
copyright © 萬盛學電腦網 all rights reserved