查询时间段数据有关问题

查询时间段数据问题

SQL code

select * from dbo.Tab_Article where ADate between '2009-09-14' and '2009-09-14'

假设前台文本框日历控件选择的开始是2009-09-14并且选择结束时间是2009-09-14,
那么只会查询出2009-09-14 0:00:00至2009-09-14 0:00:00的时间段数据,
但是我想要的是2009-09-14至2009-09-14 23:59:59:999之间的数据
该怎么写方便?




[解决办法]
where day(ADate)=14 and month(ADate)=9 and year(ADate)=2009
或者直接多写一天呗，where ADate between '2009-09-14' and '2009-09-15

[解决办法]

SQL code

declare @sdt datetime ,@edt datetimeset @sdt='2009-09-14'set @edt='2009-09-14'select 精确毫秒=dateadd(ms,-1,dateadd(dd,1,@edt)),精确到秒=dateadd(ss,-1,dateadd(dd,1,@edt))/*精确毫秒                                                   精确到秒                                                   ------------------------------------------------------ ------------------------------------------------------ 2009-09-15 00:00:00.000                                2009-09-14 23:59:59.000（所影响的行数为 1 行）*/--所以只能精确到秒，修改如下select * from dbo.Tab_Article where ADate between @sdt and dateadd(ss,-1,dateadd(dd,1,@edt)
[解决办法]
SQL code
if object_id('tb') is not  null  drop table tbcreate table tb(dt datetime)insert tbselect '2009-10-20 12:52:52' union allselect '2009-10-20 00:00:00' union allselect '2009-10-20 23:59:59' union allselect '2009-10-21 00:00:00' union allselect '2009-10-21 12:52:52'select * from tb where dt>='2009-10-20' and dt<'2009-10-21'/*2009-10-20 12:52:52.0002009-10-20 00:00:00.0002009-10-20 23:59:59.000*/select * from tb where dt between '2009-10-20' and '2009-10-21'/*2009-10-20 12:52:52.0002009-10-20 00:00:00.0002009-10-20 23:59:59.0002009-10-21 00:00:00.000*/
[解决办法]
select * from dbo.Tab_Article where ADate between @startdate and dateadd(day,1,@enddate)

[解决办法]
select * from dbo.Tab_Article where ADate between '2009-09-14' and '2009-09-14'
select * from dbo.Tab_Article where ‘2009-09-14 00：00：00 ’《=ADate《=’2009-09-14：23：59：59' 

 

查看更多 下一篇