MS SQL DATEPART 與 DATENAME 深度解析：從語法差異到交叉表實戰

實戰案例：動態生成「城市 × 星期」交叉統計表

源數據：

目標報表：

方案 A：DATEPART (dw) + 條件聚合 (性能最優，推薦)

-- 建表
CREATE TABLE #t (id int, city varchar(2), dt datetime);
INSERT INTO #t VALUES
(1,'a','20050124'),(2,'b','20050123'),(3,'b','20050123'),
(4,'a','20050121'),(5,'d','20050120'),(6,'c','20050119'),
(7,'c','20050119'),(8,'a','20050118');

-- 核心查詢 (假設 DATEFIRST=7, 週日=1)
SELECT
    city AS [City\Day],
    SUM(CASE WHEN DATEPART(dw, dt) = 2 THEN 1 ELSE 0 END) AS Mon,  -- 週一=2
    SUM(CASE WHEN DATEPART(dw, dt) = 3 THEN 1 ELSE 0 END) AS Tue,
    SUM(CASE WHEN DATEPART(dw, dt) = 4 THEN 1 ELSE 0 END) AS Wed,
    SUM(CASE WHEN DATEPART(dw, dt) = 5 THEN 1 ELSE 0 END) AS Thu,
    SUM(CASE WHEN DATEPART(dw, dt) = 6 THEN 1 ELSE 0 END) AS Fri,
    SUM(CASE WHEN DATEPART(dw, dt) = 7 THEN 1 ELSE 0 END) AS Sat,
    SUM(CASE WHEN DATEPART(dw, dt) = 1 THEN 1 ELSE 0 END) AS Sun,
    COUNT(*) AS Total
FROM #t
GROUP BY city
UNION ALL
SELECT
    'Total',
    SUM(CASE WHEN DATEPART(dw, dt) = 2 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 3 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 4 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 5 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 6 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 7 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEPART(dw, dt) = 1 THEN 1 ELSE 0 END),
    COUNT(*)
FROM #t;

方案 B：DATENAME (dw) + 條件聚合 (可讀性強，受語言環境影響)

SELECT
    city AS [City\Day],
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期一' THEN 1 ELSE 0 END) AS Mon,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期二' THEN 1 ELSE 0 END) AS Tue,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期三' THEN 1 ELSE 0 END) AS Wed,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期四' THEN 1 ELSE 0 END) AS Thu,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期五' THEN 1 ELSE 0 END) AS Fri,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期六' THEN 1 ELSE 0 END) AS Sat,
    SUM(CASE WHEN DATENAME(dw, dt) = N'星期日' THEN 1 ELSE 0 END) AS Sun,
    COUNT(*) AS Total
FROM #t
GROUP BY city
UNION ALL
SELECT 'Total', ... ; -- 同理

關鍵警示： DATENAME 返回值取決於 SET LANGUAGE / 登錄語言。中文環境返回 N'星期一'，英文環境返回 'Monday'。跨環境部署必須用 DATEPART 或顯式 SET LANGUAGE。

現代替代方案：PIVOT 操作符 (SQL 2005+)

SELECT City\Day, ISNULL([1],0) AS Sun, ISNULL([2],0) AS Mon, ISNULL([3],0) AS Tue,
       ISNULL([4],0) AS Wed, ISNULL([5],0) AS Thu, ISNULL([6],0) AS Fri, ISNULL([7],0) AS Sat,
       ([1]+[2]+[3]+[4]+[5]+[6]+[7]) AS Total
FROM (
    SELECT city, DATEPART(dw, dt) AS dw
    FROM #t
) src
PIVOT (COUNT(dw) FOR dw IN ([1],[2],[3],[4],[5],[6],[7])) pvt;

總結與選型指南

掌握 DATEPART (整數計算) 與 DATENAME (字串顯示) 的分工本質，並始終顯式設置 SET DATEFIRST 7 統一週起始日，是編寫健壯、可移植、高性能日期邏輯的基石。現代 T-SQL 開發請優先考慮 PIVOT 替代手工條件聚合，讓優化器為你做更好的執行計劃選擇。