declare @table table(begindate datetime,enddate datetime) insert into @table select '2010-3-16 4:8:8','2010-3-17 4:8:8' union all select '2010-3-17 6:8:8','2010-3-17 6:58:8' union all select '2010-3-17 14:8:8','2010-3-18 4:8:8' union all select '2010-3-18 14:8:8','2010-3-20 4:8:8' declare @date datetime set @date='2010-3-17 7:0:0'--你想查询的数据:将产生16日7点到17日7点的数据select sum(datediff(minute,begindate,enddate)) as minutecount from @table where begindate>=dateadd(day,-1,@date) and enddate<@date
declare @table table(begindate datetime,enddate datetime) insert into @table select '2010-3-16 5:00:00','2010-3-16 8:00:00' union all select '2010-3-16 5:00:00','2010-3-17 6:00:00' union all select '2010-3-16 5:00:00','2010-3-17 8:00:00' union all select '2010-3-16 8:00:00','2010-3-16 15:00:00' union all select '2010-3-16 8:00:00','2010-3-17 6:00:00' union all select '2010-3-16 8:00:00','2010-3-17 8:00:00' union all select '2010-3-17 5:00:00','2010-3-17 6:00:00' union all select '2010-3-17 5:00:00','2010-3-17 8:00:00'
我用的这个表达式,SELECT sum(DATEDIFF(mi,impletime,findate)) from msafety where impletime >= CONVERT(datetime,CONVERT(char(10),getdate()-1,120) + ' 7:00:00',120) and findate <= CONVERT(datetime,CONVERT(char(10),getdate(),120) + ' 7:00:00',120) 可以求每天7点到次日7点的分钟总和,但是还有一个问题是如果开始时间在7点前输入,结束时间在7点后,例如一个工作是在6:30AM开始,8:30AM结束,属于跨了两个结点,这个6:30-8:30是出现在当日查询里面,还是出现在次日查询里面呢?
问题已经解决了。SELECT sum(DATEDIFF(mi,开始时间,结束时间)) from msafety where impletime >= CONVERT(datetime,CONVERT(char(10),getdate()-1,120) + ' 7:00:00',120) and findate <= CONVERT(datetime,CONVERT(char(10),getdate(),120) + ' 7:00:00',120)万分多谢各位!
开始 结束
2010-3-13 13:30:00:000 2010-3-14 04:30:00:000
2010-3-14 8:00:00:000 2010-3-14 19:25:00:000
我想计算2010-3-13的早7:00至2010-3-14的早7:00(精确到分钟)之间的总分钟数还有一个问题,比如3月17日,判断范围是3-16的7:00AM至3-17的7:00AM,到了3月18,就是3-17的7:00AM至3-18的7:00AM了。只有时间不变,而日期是递增+1
SELECT CONVERT(datetime,CONVERT(char(10),getdate(),120) + ' 7:00:00',120)
自己把7:00加上哦,呵呵
insert into @table
select '2010-3-16 4:8:8','2010-3-17 4:8:8' union all
select '2010-3-17 6:8:8','2010-3-17 6:58:8' union all
select '2010-3-17 14:8:8','2010-3-18 4:8:8' union all
select '2010-3-18 14:8:8','2010-3-20 4:8:8' declare @date datetime
set @date='2010-3-17 7:0:0'--你想查询的数据:将产生16日7点到17日7点的数据select sum(datediff(minute,begindate,enddate)) as minutecount
from @table
where begindate>=dateadd(day,-1,@date)
and enddate<@date
declare @table table(begindate datetime,enddate datetime)
insert into @table
select '2010-3-16 5:00:00','2010-3-16 8:00:00' union all
select '2010-3-16 5:00:00','2010-3-17 6:00:00' union all
select '2010-3-16 5:00:00','2010-3-17 8:00:00' union all
select '2010-3-16 8:00:00','2010-3-16 15:00:00' union all
select '2010-3-16 8:00:00','2010-3-17 6:00:00' union all
select '2010-3-16 8:00:00','2010-3-17 8:00:00' union all
select '2010-3-17 5:00:00','2010-3-17 6:00:00' union all
select '2010-3-17 5:00:00','2010-3-17 8:00:00'
可以求每天7点到次日7点的分钟总和,但是还有一个问题是如果开始时间在7点前输入,结束时间在7点后,例如一个工作是在6:30AM开始,8:30AM结束,属于跨了两个结点,这个6:30-8:30是出现在当日查询里面,还是出现在次日查询里面呢?