[SQL] 日期 - 最近的星期一

• 一個星期的第一天 DateFirst

利用 @@DATEFIRST 來查詢 SET DATEFIRST 設定星期的第一天為哪一天，而第一天也會受到語系影響而有所不同，以下面的例子來說明

-- 20120413 為星期五
SET LANGUAGE Italian;
SELECT @@DATEFIRST AS [義大利第一天] -- 預設為 1 (星期一)
SELECT DATEPART(dw,'20120413') AS [dw 值] -- dw 值為 5
GO
SET LANGUAGE us_english;
SELECT @@DATEFIRST AS [美國第一天] -- 預設為 7 (星期日)
SELECT DATEPART(dw,'20120413') AS [dw 值] -- dw 值為 6
GO 
SET LANGUAGE '繁體中文'
SELECT @@DATEFIRST  AS [繁體中文第一天] -- 預設為 7 (星期日)
SELECT DATEPART(dw,'20120413') AS [dw 值] -- dw 值為 6

執行上述 T-SQL 語法可以看出第一天是哪一天對 DATEPART 取 dw 值的影響。

SQL Server 內全部語系第一天，預設不是 1 (星期一) 就是 7 (星期日)，可以利用 sys.syslanguages 來查詢全部語系第一天的預設值

SELECT * FROM sys.syslanguages

下面說明以繁體中文語系、第一天預設值為 7 (星期日) 來說明主題。

從 20120413 開始，利用 CTE 往後建立 10 天資料

;
WITH CTE
AS
(
    SELECT CAST('20120413' AS datetime) AS [Date]
    UNION ALL
    SELECT DATEADD(dd,1,DATE)
    FROM CTE
    WHERE Date <= GETDATE() + 10 -- 產生 10 天資料
)
SELECT
  [Date] AS 日期,
  DATENAME(dw,[Date]) AS [星期],
  DATEADD(dd, ((7 - DATEPART(dw,[Date])) + 2) % 7,[Date]) AS [最接近的星期一日期]
FROM CTE              

• 簡易分析 ((7 - DATEPART(dw,[Date])) + 2) % 7

(
   (7 - DATEPART(dw,[Date])) -- Step 1 
   + 2                       -- Step 2       
) % 7                        -- Step 3

Step 1 ：計算該日期至最後一天（星期六，dw 值為 7）還需要多 dw 值才能補足完整一星期
Step 2 ：根據目標星期為何，加上該目標星期 dw 值
Step 3 ：Step1 和 Step 2 dw 值總和可能超過 7 天，利用 % 計算其餘數

• 利用20120415（星期日）來舉例說明

((Step 1 dw 值為 6 ) + ( Step 2 dw 值為 2)) % 7 = 1，因此 20120415（星期日）最近的星期一為 20120416。

• 全部結果

延伸閱讀：
• [SQL] 日期 - 基本應用

參考資料：
• 如何用簡單的 SQL 技巧取得特定日期是否為週末假日 
• MSND：@@DATEFIRST、SET DATEFIRST、DATEPART、sys.syslanguages
• 查出每週第一天日期的方法?