比如说你想知道客户第12周的数据select * from 表 where datepart(week,[时间列])=12 如果要查本周的select * from 表 where datepart(week,[时间列])=datepart(week,getdate())网上找的 试试吧
今日数据 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)
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) --本周
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)
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) --本周