select * from tb where 时间字段 between '2009-10-1' and '2009-10-14' and datepart(day,时间字段) in (20,21,22.................)
union all select * from tb where time between 2009-10-1 20:00:00 and 2009-10-2 04:00:00 union all select * from tb where time between 2009-10-2 20:00:00 and 2009-10-3 04:00:00 union all ............. :-)
应该是 datepart(HH,时间字段) in 吧?
declare @date1 datetime,@date2 datetime set @date1 = '2009-10-01' set @date2 = '2009-10-14'SELECT dateadd(day,datediff(day,'1900-1-1',@date1),'1900-1-1 20:00:00') as [StartTime] ,dateadd(day,datediff(day,'1900-1-1',@date2),'1900-1-1 04:00:00') as [EndTime]我一般这样处理
[time] between '2009-10-1' and '2009-10-14' and convert(char(8),[time],108) >'20:00:00' and convert(char(8),[time],108)<'04:00:00'
重来 select * from tb where 时间字段 between '2009-10-1' and '2009-10-14' and datepart(hh,时间字段) in (20,21,22.................)
我想统计某段日期特定时间段的数据,怎么写控制条件? 比如统计2009-10-1 到 2009-10-14 这段日期内 第一天 20:00:00 到第二天凌晨 4:00:00 这个时间段的数据 WHERE date between '2009-10-01 20:00:00' and '2009-10-14 04:00:00'
select * from tb where 时间字段 between '2009-10-1 20:30:00' and '2009-10-14 4:20:00' and datepart(day,时间字段) in (20,21,22.................)这样不就可以了
或直接取相应时间段就行。 用datepart就可以了吗,只不过条件写的长一些。
如果是2号的21点,这样比较会出问题,因为convert(char(8),[time],108) >'20:00:00' and convert(char(8),[time],108) <'04:00:00'21:00:00>'04:00:00' 而我这里的04:00:00是只相邻第二天凌晨4点 ,也就是3号的4点 我要问的就是这个大小条件怎么限制
select * from tb where convert(varchar(13),时间字段,120) between '2009-10-01 20' and '2009-10-14 04' and (datepart(hh,时间字段) btween 0 and 4 or datepart(hh,时间字段) between 20 and 23)
小于 date+1 的4点
where 时间字段 between '2009-10-1' and '2009-10-14'
and datepart(day,时间字段) in (20,21,22.................)
select * from tb where time between 2009-10-1 20:00:00 and 2009-10-2 04:00:00
union all
select * from tb where time between 2009-10-2 20:00:00 and 2009-10-3 04:00:00
union all
.............
:-)
吧?
set @date1 = '2009-10-01'
set @date2 = '2009-10-14'SELECT dateadd(day,datediff(day,'1900-1-1',@date1),'1900-1-1 20:00:00') as [StartTime]
,dateadd(day,datediff(day,'1900-1-1',@date2),'1900-1-1 04:00:00') as [EndTime]我一般这样处理
and convert(char(8),[time],108)<'04:00:00'
select * from tb
where 时间字段 between '2009-10-1' and '2009-10-14'
and datepart(hh,时间字段) in (20,21,22.................)
比如统计2009-10-1 到 2009-10-14 这段日期内
第一天 20:00:00 到第二天凌晨 4:00:00 这个时间段的数据
WHERE date between '2009-10-01 20:00:00' and '2009-10-14 04:00:00'
where 时间字段 between '2009-10-1 20:30:00'
and '2009-10-14 4:20:00'
and datepart(day,时间字段) in (20,21,22.................)这样不就可以了
用datepart就可以了吗,只不过条件写的长一些。
and convert(char(8),[time],108) <'04:00:00'21:00:00>'04:00:00'
而我这里的04:00:00是只相邻第二天凌晨4点 ,也就是3号的4点
我要问的就是这个大小条件怎么限制
from tb
where convert(varchar(13),时间字段,120) between '2009-10-01 20' and '2009-10-14 04'
and (datepart(hh,时间字段) btween 0 and 4 or datepart(hh,时间字段) between 20 and 23)