我的表设计成这样的,根据日期段来查询.怎么解决?高手指点!!!
table:
id startdate enddate field
1 2007-1-1 2007-1-1 aaf
2 2007-1-2 2007-1-3 ads
3 2007-1-13 2007-1-14 afds
4 2007-1-14 2007-1-14 asfss
5 2007-1-26 2007-1-27 adfsdfx
....
查询2007-1-1到2007-1-3之间的数据:
得到如下记录:
1 2007-1-1 2007-1-1 aaf
2 2007-1-2 2007-1-3 ads
查询2007-1-2到2007-1-5之间的数据:
得到如下记录:
2 2007-1-2 2007-1-3 ads
-------
就是怎么实现时间段查询啊,有两个日期在的时候..
[解决办法]
因为startdate总是小于等于enddate
所以可以这样写:
查询2007-1-1到2007-1-3之间的数据:
select * from tablename where startdate > = '2007-1-1 ' and enddate <= '2007-1-3 '
查询2007-1-2到2007-1-5之间的数据:
select * from tablename where startdate > = '2007-1-2 ' and enddate <= '2007-1-5 '
[解决办法]
改个地方
select * from tablename where (startdate between l_ksrq and l_jsrq) or (enddate between l_ksrq and l_jsrq)
[解决办法]
if object_id( 'T_test ') is not null
begin
drop table t_test
end
create table t_test
(
id int ,
startdate datetime ,
enddate datetime ,
field varchar(20)
)
insert into t_Test
select 1 , '2007-1-1 ', '2007-1-1 ', 'aaf ' union
select 2 , '2007-1-2 ', '2007-1-3 ', 'ads '
select *
from t_Test
where startdate > = @start_date
and enddate <= @end_date
[解决办法]
try:
select *
from t_test
where ( startdate between @start_date and @end_date )
or ( enddate between @start_date and @end_date )
