sql,时间段between and 时间段查询

[jxftp]

不知道为什么查询不出来，
表中有一两个字段，开始时间(Fstartdate)，结束时间(Fenddate)
如2013-1-27 ,2014-1-27
今天是1月27日，我希望能查到今天的记录，我查询的条件是：
select * from tablename where 1=1 and getdate() between fstartdate and Fenddate

结果没有显示


我换成：
select * from tablename
where 1=1 and
getdate() between cast(convert(varchar(10),Fstartdate,120) as datetime)+''
and cast(convert(varchar(10),Fenddate,120)+'23:59:59' as datetime)

也没有显示，不知道为什么？

[發糞塗牆]

select * from tablename where 1=1 and fstartdate<= getdate() AND  Fenddate>=GETDATE()

这样呢？你的fstartdate是什么数据类型？

[昵称90天可改一次]

select * from tablename where 1=1 and getdate() between fstartdate and dateadd(day,1,Fenddate)

[LongRui888]

改成这样试试，加一个空格： select * from tablename where 1=1 and getdate() between cast(convert(varchar(10),Fstartdate,120) as datetime)+'' and cast(convert(varchar(10),Fenddate,120)+' 23:59:59' as datetime)

[昵称90天可改一次]

create table test(fstartdate datetime,Fenddate datetime)
insert into test values('2013-1-27','2014-1-27')
select * from test where 1=1 and getdate() between fstartdate and dateadd(day,1,Fenddate)
/*
2013-01-27 00:00:00.000	2014-01-27 00:00:00.000
*/

fstartdate='2013-01-27 00:00:00.000'
Fenddate='2014-01-27 00:00:00.000'
getdate()='2014-01-27 15:02:21.950'

[發糞塗牆]

select * from tablename where 1=1 and getdate() between cast(convert(varchar(10),Fstartdate,120) as datetime)+'' and cast(convert(varchar(10),Fenddate,120)+'23:59:59' as datetime) 如果用这个，需要换成这样： select * from tablename where 1=1 and getdate() between cast(convert(varchar(10),Fstartdate,120) as datetime)+' 00:00:00.000' and cast(convert(varchar(10),Fenddate,120)+' 23:59:59.997' as datetime)

[jxftp]

datetime

[發糞塗牆]

select * from tablename 
where 1=1 and
getdate() between cast(convert(varchar(30),Fstartdate,120) as datetime)+' 00:00:00.000' 
and cast(convert(varchar(30),Fenddate,120)+' 23:59:59.997' as datetime)