[SQL]取得某年每週的日期區間

2015-11-19

12531
0
SQL

要如何取得某年每週的日期區間
以 2015 年為例
第1週為 2014/12/28 到 2015/01/03
第2週為 2015/01/04 到 2015/01/10

問題

同事詢問 SQL 是否可以造出一年每週的日期區間，以 2015 年為例

第1週為 2014/12/28 到 2015/01/03

第2週為 2015/01/04 到 2015/01/10

…

分析

要達到這個目的，一開始就要先將問題拆開來解

1.如何取得第一週的開始日期(那週的星期日)及結束日期(那週的星期六)。

2.要如何一直取日期到那年結束呢?

最後再把組合起來就是我們需要的SQL。

研究

1.如何取得第一週的開始日期(那週的星期日)及結束日期(那週的星期六)。

可以參考「Find the Date range of week by current date in sql?」，

取得本週的開始日期可以用 DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) ，

取得本週的結束日期可以用 DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) , 如下，

所以如果要取得 2015 年的第1週，GetDate() 改成 2015/01/01 就可以了，如下，

2.要如何一直取日期到那年結束呢?

所以我們可以使用 CTE 來比較日期是否有小於我們要取的年度，或是直接取得 52 次，如下，


WITH seqTest
AS
(
SELECT DATEADD(wk, DATEDIFF(wk,0,'2015/1/1'), -1) AS startDate, 0 AS seq
UNION ALL
SELECT DATEADD(wk, DATEDIFF(wk,0,'2015/1/1') + seq + 1, -1) AS startDate, seq + 1 AS seq 
FROM seqTest sq
WHERE seq < 52
)
SELECT *
FROM seqTest;

所以組合起來如下，


-- 取得某年每週的日期區間
-- 例如 2015 年
-- 第 1 周 2014-12-28 ~ 2015-1-3
-- 第 2 周 2015-1-4 ~ 2015-1-10 
DECLARE @year int  
set @year = 2015;

WITH YearWeekDateRange
AS
(
SELECT DATEDIFF(wk,0, STR(@year) + '/1/1') AS startWeekNum
, DATEADD(wk, DATEDIFF(wk,0, STR(@year) + '/1/1'), -1) AS startDate
, 0 AS seq
UNION ALL
SELECT startWeekNum
, DATEADD(wk, startWeekNum + seq + 1, -1) AS startDate
, seq + 1 AS seq 
FROM YearWeekDateRange YW
WHERE seq < 52
)
--這裡再補上該週的最後一天的日期(endDate)
SELECT startDate,DATEADD(wk, startWeekNum + seq, 5) AS endDate, seq   
FROM YearWeekDateRange;

那如果要直接取得該年度的每週日期，則可以直接用 Year(GETDATE()) 去代替 @year 變數，如下，


WITH YearWeekDateRange
AS
(
SELECT DATEDIFF(wk,0, STR(YEAR(GETDATE())) + '/1/1') AS startWeekNum
, DATEADD(wk, DATEDIFF(wk,0, STR(YEAR(GETDATE())) + '/1/1'), -1) AS startDate
, 0 AS seq
UNION ALL
SELECT startWeekNum
, DATEADD(wk, startWeekNum + seq + 1, -1) AS startDate
, seq + 1 AS seq 
FROM YearWeekDateRange YW
WHERE seq < 52
)
--這裡再補上該週的最後一天的日期(endDate)
SELECT startDate,DATEADD(wk, startWeekNum + seq, 5) AS endDate, seq   
FROM YearWeekDateRange;

大家如果有更好的作法，也請讓分享給大家知道哦!