怎么查询本周数据?

[viphk]

表里有个时间字段,time
怎么查询今日数据,昨日数据,本周数据?
本周数据不是指7个的数据,是自然周期,
怎么写SQL语句的呢?

[luofenghen]

比如说你想知道客户第12周的数据

select * from 表 where datepart(week,[时间列])=12

如果要查本周的

select * from 表 where datepart(week,[时间列])=datepart(week,getdate())

网上找的 试试吧

[liang145]

create table #Time(date datetime)
insert #Time
select '2011-04-16' union all
select '2011-04-17' union all
select '2011-04-18' union all
select '2011-04-19' union all
select '2011-04-20' union all
select '2011-05-07' union all
select '2011-05-08' union all
select '2011-05-09' union all
select '2011-05-11' 

declare @Today as datetime
set @Today=getdate()
select * from #Time where convert(nvarchar(10),date,120)=convert(nvarchar(10),@Today,120)  --今天

select * from #Time where convert(nvarchar(10),date,120)=convert(nvarchar(10),dateadd(day,-1,@Today),120)  --昨天

select * from #Time where datepart(week,date)=datepart(week,@Today) --本周

[viphk]

谢谢大家!

[dawugui]

[Quote=引用楼主 viphk 的回复:]
表里有个时间字段,time
怎么查询今日数据,昨日数据,本周数据?
本周数据不是指7个的数据,是自然周期,
怎么写SQL语句的呢?
[/Quote]
今日数据
select * from tb where datediff(dd,time,getdate()) = 0

昨日数据
select * from tb where datediff(dd,time,getdate()) = 1

本周数据
按照西方习惯
select * from tb where datediff(week,time,getdate()) = 0
按照中国习惯
select * from tb where (datediff(week,time,getdate()) = 0 and datepart(dw,time) > 1) or (datediff(week,time,getdate()) = 1 and datepart(dw,time) = 1)