在SQL Server中使用DATEDIFF计算工作时间
在SQL Server中使用DATEDIFF计算工作时间
提问于 2018-06-21 14:32:16
我现在很难计算出员工的总工作时间，特别是上夜班的人。下面是我的代码：
DECLARE @StartTime time
DECLARE @EndTime time
SELECT @StartTime = CAST('Jun 04 2018 8:00PM' as time)
SELECT @EndTime = CAST('Jun 04 2018 5:00AM' as time)
SELECT
SUM((DATEDIFF(MINUTE, @StartTime, @EndTime) - isnull(datediff(minute, break_start, break_end), 0))/ 60.0)
FROM pmis.dbo.employee as a
LEFT JOIN pmis.dbo.time_reference as b ON b.code = ISNULL(a.TimeReference, 'TIME14')
cross apply
(
select break_start = case when b.break_from between @StartTime and @EndTime
then b.break_from
when @StartTime between b.break_from and b.break_to
then @StartTime
else NULL
end,
break_end = case when b.break_to between @StartTime and @EndTime
then b.break_to
when @EndTime between b.break_from and b.break_to
then @EndTime
end
) as d
WHERE
a.Shift = 0 and a.eid = 7078当给定的时间是这样的时候，我得到的是
@StartTime = 8:00 AM
@EndTime = 5:00 PM那么结果就是我想要的8.00 hrs，但是如果是这样的话
@StartTime = 8:00 PM
@EndTime = 5:00 AM然后我得到-15.00怎么解决这个问题呢？谢谢
回答 2
Stack Overflow用户
发布于 2018-06-21 15:51:02
如果endtime早于starttime，请尝试在计算中添加1天(1440分钟)。这将不会处理墓地轮班的休息。但是您之前的代码也有同样的问题
SELECT
SUM((DATEDIFF(MINUTE, @StartTime, @EndTime)+
CASE WHEN @StartTime > @EndTime THEN 1440 ELSE 0 END -- CHANGE IN CODE IS HERE
- isnull(datediff(minute, break_start, break_end), 0))/ 60.0)
FROM pmis.dbo.employee as a
LEFT JOIN pmis.dbo.time_reference as b
ON b.code = ISNULL(a.TimeReference, 'TIME14')
cross apply
(
select break_start = case when b.break_from between @StartTime and @EndTime
then b.break_from
when @StartTime between b.break_from and b.break_to
then @StartTime
else NULL
end,
break_end = case when b.break_to between @StartTime and @EndTime
then b.break_to
when @EndTime between b.break_from and b.break_to
then @EndTime
end
) as d
WHERE
a.Shift = 0 and a.eid = 7078Stack Overflow用户
发布于 2018-06-21 14:43:52
另一种解决方法是使用datetime数据类型而不是time，然后需要更改这些行
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = CAST('Jun 04 2018 8:00PM' as datetime)
SELECT @EndTime = CAST('Jun 04 2018 5:00AM' as datetime)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50961806
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号