如何找出給定日期的該週起始日期
Tuesday, October 14, 2008 by Unknown
網路上常有一些公開的行程表(schedule)，一般會列出好幾筆最近的行程(可能橫跨過去現在未來的日期)，如果要把這一週的行程做強調(highlight)的效果，我們勢必要知道一個日期(given date)在當週的頭(Week Begin Date)尾(Week End Date)日期。
上圖是 Roger Federer 的行程表，像是10月13號他在馬德里打球，我個人會比較傾向把這一行做強調，讓瀏覽者一眼就看的到他現在到底在哪打球，所以系統必須要能判斷現在的時間點是否要若在該行程那一週，若是有的話就顯示，沒有的話就找下一個賽事的行程。
拜了一下股溝大神之後，有神人在 2004 年就弄出來了，請參閱以下的連結：
Raj Chidipudi：Getting the Week Start Date and Week End Date in SQL
有一些很陌生的 SQL function，所以以下是自己加上的註解，若要改成 Stored Procedure 的話也是很簡單的，好用喔。
DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2008-10-13T00:00:00'
SELECT @WEEK_BEGINING = 'MONDAY'
這邊就是單純的宣告變數，@REPORT_DATE 就是我們所給予的日期(例如可以把費神的行程中任一天丟進去)，而 @WEEK_BEGINING 則是我們告訴系統一星期開始的第一天是星期幾，你可以設定任一個星期，一般我是用星期天，那你只要把他改成 SUNDAY 就可以，純粹只是變數內容而已。
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
接下來這個比較有趣，有一個 DATEFIRST，MSDN 中文版上的白話文是「將一週的第一天設為 1-7 其中一個數字」。所以如果你和我一樣設定成 SUNDAY 的話，這時候的 DATEFIRST 會是 7。
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
上面的查詢句就是重點了，他的大意就是用使用者給定的日期去減掉 (運算式) 來對日期對往前或往後的動作。而 SELECT (DATEPART(DW, @REPORT_DATE) - 1) 這一句是指我給定的日期這一天是代表一個星期中的第幾天，由於是往前取日期，所以才預先 -1，所以反之如果要算該日期的當週最後一天的日期時，就是用 7 去減就可以得到值，再加上原本給定的日期，就會得到往後推遲幾天的日期了。DATEPART 中的 DW 是指 weekday，詳細說明請參閱這裡。
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)
最後作者是列印出結果，當然你也可以把他改成 SELECT 這樣程式執行完 SP 後就可以得到這兩個起迄日期的值。
以上是自己的小小筆記，年紀大了需要寫下來才不會忘記，若是有相同情境的需求，這個 SP 應該可以適用喔 ^_^