基礎函數已掌握,本文進階至 **財務週期計算、動態時間窗口、跨時區業務、效能優化模式**,解決實際專案中棘手的日期邏輯。
-- 月末最後一天 (通用)
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);
-- 當前日期完整拆解
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;
-- 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;