declare @t table(id int,dt datetime) insert @t select 1,'2007-06-08' union all select 2,'2007-06-09' union all select 3,'2007-06-10' union all select 4,'2007-06-11' union all select 5,'2007-06-12'select * from @t where datepart(week,dt) = datepart(week,getdate())/* id dt ----------- ------------------------- 3 2007-06-10 00:00:00.000 4 2007-06-11 00:00:00.000 5 2007-06-12 00:00:00.000 */
补充,也可以 select * from @t where datediff(ww,dt,getdate())=0
between '星期起始日期' and '星期结束日期'
一楼的有些问题,会取出其他年的数据,因为datepart(week,'2001-1-1') = datepart(week,'2007-1-1') 二楼的问题在于每周的开始是周日而不是周一借用一楼的表 set datefirst 1 select * from @t where datepart(week,dt) = datepart(week,getdate()) and datepart(year,dt) = datepart(year,getdate())
declare @t table(id int,dt datetime) insert @t select 1,'2007-06-08' union all select 2,'2007-06-09' union all select 3,'2007-06-10' union all select 4,'2007-06-11' union all select 6,'2001-06-11' union all select 5,'2007-06-12' select * from @t where datepart(week,dt) = datepart(week,getdate()) and datepart(year,dt) = datepart(year,getdate())
--查询上周的数据 select * from table where date > DATEADD(wk, DATEDIFF(wk,0,getdate()),-7) and date < DATEADD(wk, DATEDIFF(wk,0,getdate()),0) --查询本周的数据 select * from table where date > DATEADD(wk, DATEDIFF(wk,0,getdate()),0) --查询指定某周的数据 select * from table where datepart(week,date ) = n
insert @t
select 1,'2007-06-08' union all
select 2,'2007-06-09' union all
select 3,'2007-06-10' union all
select 4,'2007-06-11' union all
select 5,'2007-06-12'select * from @t where datepart(week,dt) = datepart(week,getdate())/*
id dt
----------- -------------------------
3 2007-06-10 00:00:00.000
4 2007-06-11 00:00:00.000
5 2007-06-12 00:00:00.000
*/
select * from @t where datediff(ww,dt,getdate())=0
二楼的问题在于每周的开始是周日而不是周一借用一楼的表
set datefirst 1
select * from @t where datepart(week,dt) = datepart(week,getdate()) and datepart(year,dt) = datepart(year,getdate())
insert @t
select 1,'2007-06-08' union all
select 2,'2007-06-09' union all
select 3,'2007-06-10' union all
select 4,'2007-06-11' union all
select 6,'2001-06-11' union all
select 5,'2007-06-12'
select * from @t where datepart(week,dt) = datepart(week,getdate()) and datepart(year,dt) = datepart(year,getdate())
select datepart(ww,'2007-06-10') true
24select datediff(ww,'2007-06-10','2007-06-11') false
0
datepart(week,dt)类似这样的结构可能会造成全表扫描吧。
select * from table where date > DATEADD(wk, DATEDIFF(wk,0,getdate()),-7) and date < DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
--查询本周的数据
select * from table where date > DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
--查询指定某周的数据
select * from table where datepart(week,date ) = n