本來想偷懶 google 一下,沒有滿意的結果,好像他自己也沒底!思路也不一樣! 《求一尖酸的算法》(有點兒同感,說起來簡單,做起來就暈) http://www.itpub.net/252645.html
順便說一下,我寫程序總想寫的"更通用"一些,可能答非所問了!
由于 @@datefirst 可通過 set datefirst n 設定 導致 datepart(weekday,[date]) 不確定! 所以用 datename! 因為確定的日期是周幾肯定是永遠不變的!當然不隨 datefirst 變! 所以更要用 datename! 另外按著中國人的習慣: 周日算作上周的最后一天! 如果是其他語言版本的 sql server 注意選用該語言版本的 周幾 的形式!
declare @b datetime declare @e datetime
set @b = '2004-07-29' set @e = '2004-08-05'
select @b as 開始日期,@e as 結束日期, datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1 as 跨周數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期二','星期三','星期四','星期五','星期六','星期日') then 1 else 0 end as 周一個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期三','星期四','星期五','星期六','星期日') then 1 else 0 end - case when datename(weekday,@e) in ('星期一') then 1 else 0 end as 周二個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期四','星期五','星期六','星期日') then 1 else 0 end - case when datename(weekday,@e) in ('星期一','星期二') then 1 else 0 end as 周三個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期五','星期六','星期日') then 1 else 0 end - case when datename(weekday,@e) in ('星期一','星期二','星期三') then 1 else 0 end as 周四個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期六','星期日') then 1 else 0 end - case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四') then 1 else 0 end as 周五個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@b) in ('星期日') then 1 else 0 end - case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五') then 1 else 0 end as 周六個數
,datediff(week ,case when datename(weekday,@b) = '星期日' then @b - 1 else @b end ,case when datename(weekday,@e) = '星期日' then @e - 1 else @e end ) + 1
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五','星期六') then 1 else 0 end
