请问如何查询离当天7天人记录
如:
id appdate pname
1 2010-4-1 A1
2 2010-4-2 A2
3 2010-4-3 A3
4 2010-4-4 A4
5 2010-4-5 A5
6 2010-4-6 A6
7 2010-4-7 A7
8 2010-4-8 A8
9 2010-4-9 A9
.....
若今天是8号,那就要把id为1到7的记录查出。用select * from tbname where (getdate()-appdate)='7' ,这样可以吗?
如:
id appdate pname
1 2010-4-1 A1
2 2010-4-2 A2
3 2010-4-3 A3
4 2010-4-4 A4
5 2010-4-5 A5
6 2010-4-6 A6
7 2010-4-7 A7
8 2010-4-8 A8
9 2010-4-9 A9
.....
若今天是8号,那就要把id为1到7的记录查出。用select * from tbname where (getdate()-appdate)='7' ,这样可以吗?
insert #tt select 2 ,'2010-4-2','A2'
insert #tt select 3 ,'2010-4-3','A3'
insert #tt select 4 ,'2010-4-4','A4'
insert #tt select 5 ,'2010-4-5','A5'
insert #tt select 6 ,'2010-4-6','A6'
insert #tt select 7 ,'2010-4-7','A7'
insert #tt select 8 ,'2010-4-8','A8'
insert #tt select 9 ,'2010-4-9','A9'select * from #tt where DATEDIFF(day,appdate,GETDATE())between 0 and 6 id appdate pname
----------- ---------- ----------
1 2010-4-1 A1
2 2010-4-2 A2
3 2010-4-3 A3
4 2010-4-4 A4
5 2010-4-5 A5
6 2010-4-6 A6
7 2010-4-7 A7(7 行受影响)
---上面是可以的.但是效率不高,在列上用表达式是不推荐的
select * from tbname where appdate between convert(varchar(10),getdate()-7,120)+' 0:00:00' and getdate()
这样可以,如果appdate上面有索引,这样是可以利用到的.
select * from tb where appdate between convert(varchar,getdate()-7,23) and convert(varchar,getdate()-1,23)7楼修改,代码不够严密。
select * from DBNAME
where convert(char(8),APPDATE,112) BETWEEN convert(char(8),GETDATE()-7,112) AND convert(char(8),GETDATE()-1,112)
最好是采用 between and方式
SELECT * FROM table
WHERE appdate
between CONVERT(varchar,year(getDate()))+'-'+CONVERT(varchar,month(getDate()))+'-'+CONVERT(varchar,day(getDate())-7)
and CONVERT(varchar,year(getDate()))+'-'+CONVERT(varchar,month(getDate()))+'-'+CONVERT(varchar,day(getDate()))
应用datediff函数计算两日期相差的天数