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

最新推荐文章于 2021-08-19 20:40:01 发布

weixin_33962923

最新推荐文章于 2021-08-19 20:40:01 发布

阅读量323

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/songhaipeng/p/4201360.html

版权

有关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()))