萬盛學電腦網

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

SQL Server 日期時間函數進階:DATEADD/DATEDIFF/DATEPART/DATENAME 深度應用與陷阱

超越基礎:複雜業務週期、財務日曆、時區轉換的日期運算模式

基礎函數已掌握,本文進階至 **財務週期計算、動態時間窗口、跨時區業務、效能優化模式**,解決實際專案中棘手的日期邏輯。

進階 DATEADD 模式

-- 月末最後一天 (通用)
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0));

-- 下個月第一天
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0);

-- 本季度第一天/最後一天
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0); -- 季初
SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0)); -- 季末

-- 本週一 (依 @@DATEFIRST,預設 7=週日)
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0); -- 週一 00:00:00

-- 工作日加減 (排除週末) — 純 SQL 遞歸/CTE 實現
WITH WorkDays AS (
  SELECT @StartDate AS Dt, 1 AS N
  UNION ALL
  SELECT DATEADD(DAY, 1, Dt), N + 1
  FROM WorkDays
  WHERE N < @DaysToAdd AND DATEPART(WEEKDAY, DATEADD(DAY, 1, Dt)) NOT IN (1,7) -- 排除週日/週六
)
SELECT MAX(Dt) FROM WorkDays OPTION (MAXRECURSION 0);

DATEDIFF 陷阱與精確計算

  • 跨越邊界計數:`DATEDIFF(DAY, '2023-01-01 23:59', '2023-01-02 00:01') = 1` (跨越 1 個午夜),非 24 小時
  • 精確間隔**:用 `DATEDIFF(SECOND, ...) / 86400.0` 得精確天數
  • 年齡/工齡**:必須用「生日今年是否已過」邏輯修正 `DATEDIFF(YEAR, ...)`

DATEPART/DATENAME 進階用法

-- 當前日期完整拆解
SELECT 
  DATEPART(YEAR, GETDATE()) AS Yr,
  DATEPART(QUARTER, GETDATE()) AS Qtr,
  DATEPART(MONTH, GETDATE()) AS Mo,
  DATEPART(DAY, GETDATE()) AS Dy,
  DATEPART(DAYOFYEAR, GETDATE()) AS Doy,
  DATEPART(WEEK, GETDATE()) AS Wk,
  DATEPART(WEEKDAY, GETDATE()) AS Dw, -- 1=週日
  DATENAME(WEEKDAY, GETDATE()) AS DwName, -- 星期一
  DATENAME(MONTH, GETDATE()) AS MoName; -- 五月

-- 財務季度 (非自然季度,如 4-6 月為 Q1)
SELECT CASE 
  WHEN MONTH(GETDATE()) IN (4,5,6) THEN 1
  WHEN MONTH(GETDATE()) IN (7,8,9) THEN 2
  WHEN MONTH(GETDATE()) IN (10,11,12) THEN 3
  ELSE 4 END AS FiscalQuarter;

時區感知應用 (SQL 2016+)

-- UTC 存儲,顯示轉本地
DECLARE @Utc DATETIME2 = SYSUTCDATETIME();
SELECT @Utc AT TIME ZONE 'UTC' AT TIME ZONE 'China Standard Time' AS BeijingTime;
SELECT @Utc AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS NewYorkTime;

-- 資料表欄位帶時區 (datetimeoffset)
ALTER TABLE Events ADD EventTimeUtc DATETIMEOFFSET(3) DEFAULT SYSDATETIMEOFFSET();
-- 查詢自動轉換
SELECT EventTimeUtc AT TIME ZONE 'China Standard Time' AS LocalTime FROM Events;

效能優化鐵律 (重申)

  1. WHERE 子句嚴禁對欄位用函數:`WHERE DATEPART(MONTH, CreatedAt) = 1` → 全表掃描。改用範圍查詢。
  2. 計算欄持久化 + 索引:頻繁按月/週分組的大表,加持久化計算欄 `MonthKey AS DATEPART(MONTH, CreatedAt) PERSISTED` 並建索引。
  3. 批次處理用整數鍵**:`YYYYMMDD` 整數 (`CONVERT(INT, CONVERT(CHAR(8), dt, 112))`) 連接/分區/排序最快。
copyright © 萬盛學電腦網 all rights reserved