SQLSERVER语句怎么查询一个时间段的数据

发布网友发布时间：2022-04-30 03:32

共3个回答

懂视网时间：2022-04-30 07:53

统计连续时间段数据

if OBJECT_ID(N'Test',N'U') is not null
	drop table Test
go 

create table Test(
	pscode decimal(15),
	outputcode int,
	monitortime datetime
)

insert into Test
select 4100000406,1,convert(datetime,'2015-04-01 00:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 01:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 02:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 03:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 04:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 05:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 06:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 07:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 08:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 09:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 10:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 11:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 13:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 14:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 15:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 16:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 17:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 18:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 19:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 20:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 22:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 23:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 01:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 02:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 03:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 04:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 05:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 06:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 07:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 08:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 09:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 11:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 12:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 13:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 14:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 15:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 16:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 17:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 18:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 00:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 01:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 02:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 03:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 04:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 05:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 06:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 07:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 08:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 09:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 11:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 12:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 13:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 14:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 15:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 16:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 17:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 18:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 19:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 20:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 22:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 23:00')


select pscode,outputcode,
	 convert(varchar(16),MIN(monitortime),120)+'～'+convert(varchar(16),MAX(monitortime),120) fw,
 COUNT(1) num
 from (select x.pscode,x.outputcode,x.monitortime,dateadd(HOUR,-x.orderby,x.monitortime) diff
		 from (select pscode,outputcode,monitortime,
					 ROW_NUMBER() over(partition by pscode,outputcode order by pscode,outputcode,monitortime) orderby 
				 from Test) x)y 
 group by y.pscode,y.outputcode,y.diff

SQLServer时间分段查询

标签：

热心网友时间：2022-04-30 05:01

select * from 表 where 日期字段>='开始日期' and 日期字段<='截止日期'
and convert(char(8),日期字段,108)>='开始时间' and convert(char(8),日期字段,108)<='截止时间'

例如:
select * from tb1 where dDate>='2010-11-05' and dDate<='2010-11-15'
and convert(char(8),dDate,108)>='22:30:00' and convert(char(8),dDate,108)<='23:00:00'

热心网友时间：2022-04-30 06:19

使用datename函数来取时间中分钟的值，因为整点，都是0分钟。

date表示字段

select * from table where date between '2016-10-01' and '2017-10-01'

或者

select * from table where date>'2016-10-01' and date<'2017-10-01'
两个区别不大，between包含了两头的日期，那个不包含。