SQL Server 日期時間計算：依 ISO 週編號產生週起迄區間字串

需求：將每日資料依週分組，產生「第 N 週 (MM/DD-MM/DD)」格式字串。利用 DATEPART(weekday, date) 取得週幾（受 SET DATEFIRST 影響），再用 DATEADD 推算週一與週日（或自訂起始日）日期。提供完整範例與通用化設定說明。

範例資料與目標輸出

-- 來源：time_id, time_date, year, week_of_year, day
-- 目標：time_id, time_date, year, week_of_year = '第20週 (7/9-7/17)'

核心查詢

SELECT time_id, time_date, [year],
       '第' + CAST(week_of_year AS VARCHAR(2)) + '週（' +
       CAST(MONTH(week_begin) AS VARCHAR(2)) + '/' + CAST(DAY(week_begin) AS VARCHAR(2)) + '-' +
       CAST(MONTH(week_end) AS VARCHAR(2)) + '/' + CAST(DAY(week_end) AS VARCHAR(2)) + ')' AS week_of_year
FROM (
    SELECT *,
           DATEADD(day, 1 - DATEPART(weekday, time_date), time_date) AS week_begin,
           DATEADD(day, 7 - DATEPART(weekday, time_date), time_date) AS week_end
    FROM sourcetable
) a;

自訂週起始日

SET DATEFIRST n 設定週一為 1、週日為 7（預設 7=週日）。若需週一起算，執行 SET DATEFIRST 1 後再跑上述查詢。注意 DATEFIRST 為 Session 級設定，執行時生效。