SQL Server选取本周或上一周数据

有关SQL Server中有关周的数据查询主要思路来自下面这个语句

select getdate(), dateadd(wk, datediff(wk, 0, DateAdd(Day,-1,getdate())), 0), dateadd(wk, datediff(wk, 0, DateAdd(Day,-1,getdate())), 0) + 6

这是按照中国的习惯，将一周的第一天定为周一。

依照这个为基础，就可以轻松的找到本周和上一周的数据了。

本周示例

Select a.* , datediff(wk, 0, DateAdd(d,-1, a.OppDate)) From (
Select DATEADD(Day,-0,GetDate()) As OppDate Union All
Select DATEADD(Day,-1,GetDate()) As OppDate Union All
Select DATEADD(Day,-2,GetDate()) As OppDate Union All
Select DATEADD(Day,-3,GetDate()) As OppDate Union All
Select DATEADD(Day,-4,GetDate()) As OppDate Union All
Select DATEADD(Day,-5,GetDate()) As OppDate Union All
Select DATEADD(Day,-6,GetDate()) As OppDate Union All
Select DATEADD(Day,-7,GetDate()) As OppDate Union All
Select DATEADD(Day,-8,GetDate()) As OppDate Union All
Select DATEADD(Day,-9,GetDate()) As OppDate Union All
Select DATEADD(Day,-10,GetDate()) As OppDate Union All
Select DATEADD(Day,-11,GetDate()) As OppDate Union All
Select DATEADD(Day,-12,GetDate()) As OppDate Union All
Select DATEADD(Day,-13,GetDate()) As OppDate Union All
Select DATEADD(Day,-14,GetDate()) As OppDate Union All
Select DATEADD(Day,-15,GetDate()) As OppDate Union All
Select DATEADD(Day,-16,GetDate()) As OppDate Union All
Select DATEADD(Day,-17,GetDate()) As OppDate Union All
Select DATEADD(Day,-18,GetDate()) As OppDate Union All
Select DATEADD(Day,-19,GetDate()) As OppDate) As a
Where datediff(wk, 0, DateAdd(d,-1, a.OppDate)) = DATEDIFF(wk,0,DateAdd(d,-1,GetDate()))

上一周示例

Select a.* , datediff(wk, 0, DateAdd(d,-1, a.OppDate)) From (
Select DATEADD(Day,-0,GetDate()) As OppDate Union All
Select DATEADD(Day,-1,GetDate()) As OppDate Union All
Select DATEADD(Day,-2,GetDate()) As OppDate Union All
Select DATEADD(Day,-3,GetDate()) As OppDate Union All
Select DATEADD(Day,-4,GetDate()) As OppDate Union All
Select DATEADD(Day,-5,GetDate()) As OppDate Union All
Select DATEADD(Day,-6,GetDate()) As OppDate Union All
Select DATEADD(Day,-7,GetDate()) As OppDate Union All
Select DATEADD(Day,-8,GetDate()) As OppDate Union All
Select DATEADD(Day,-9,GetDate()) As OppDate Union All
Select DATEADD(Day,-10,GetDate()) As OppDate Union All
Select DATEADD(Day,-11,GetDate()) As OppDate Union All
Select DATEADD(Day,-12,GetDate()) As OppDate Union All
Select DATEADD(Day,-13,GetDate()) As OppDate Union All
Select DATEADD(Day,-14,GetDate()) As OppDate Union All
Select DATEADD(Day,-15,GetDate()) As OppDate Union All
Select DATEADD(Day,-16,GetDate()) As OppDate Union All
Select DATEADD(Day,-17,GetDate()) As OppDate Union All
Select DATEADD(Day,-18,GetDate()) As OppDate Union All
Select DATEADD(Day,-19,GetDate()) As OppDate) As a
Where datediff(wk, 0, DateAdd(d,-1, a.OppDate)) = DATEDIFF(wk,0,DateAdd(d,-1,GetDate())) – 1