数据库中有一个时间字段 ,是操作员选择的日期之后添加到数据库中的,格式为2007-11-19
ID Date
1 2007-11-19我输入两个时间2007-11-19,2007-11-19
如果查到这个条记录?
即在界面上输入开始时间和结束时间,要查到这两个时间段之间的数据,
例如数据2007-11-19,2007-11-19,就应该查到19号的所有数据
ID Date
1 2007-11-19我输入两个时间2007-11-19,2007-11-19
如果查到这个条记录?
即在界面上输入开始时间和结束时间,要查到这两个时间段之间的数据,
例如数据2007-11-19,2007-11-19,就应该查到19号的所有数据
go
insert into table1
select 1,'2007-11-19'
union select 2,'2007-11-18'
union select 3,'2007-11-19'
union select 4,getdate()
go
select * from table1
select * from table1 where [date] between '2007-11-19' and '2007-11-20'
select * from table1 where [date] between '2007-11-19' and dateadd(d,1,'2007-11-20')
id date
----------- -----------------------
1 2007-11-19 00:00:00.000
2 2007-11-18 00:00:00.000
3 2007-11-19 00:00:00.000
4 2007-11-20 15:50:06.530(4 行受影响)id date
----------- -----------------------
1 2007-11-19 00:00:00.000
3 2007-11-19 00:00:00.000(2 行受影响)id date
----------- -----------------------
1 2007-11-19 00:00:00.000
3 2007-11-19 00:00:00.000
4 2007-11-20 15:50:06.530(3 行受影响)
go
insert into table1
select 1, '2007-11-19 '
union select 2, '2007-11-18 '
union select 3, '2007-11-19 '
union select 4,getdate()
go declare @s datetime
declare @e datetime
set @s = '2007-11-18'
set @e = '2007-11-19'select *
from table1
where datediff(d, [date] ,@s) <=0 and datediff(d, [date] ,@e) >=0 /*id date
----------- ------------------------------------------------------
1 2007-11-19 00:00:00.000
2 2007-11-18 00:00:00.000
3 2007-11-19 00:00:00.000(所影响的行数为 3 行)
*/
set @s = '2007-11-19'
set @e = '2007-11-19'select *
from table1
where datediff(d, [date] ,@s) <=0 and datediff(d, [date] ,@e) >=0
/*
id date
----------- ------------------------------------------------------
1 2007-11-19 00:00:00.000
3 2007-11-19 00:00:00.000*/
drop table table1
insert into @tb select '2007-11-20'declare @dt datetime
set @dt='2007-11-20'
select * from @tb where dt between dateadd(s,-1,@dt) and dateadd(d,1,@dt)