在Sql Server 2005中查找两个日期之间的小时持续时间的问题 [英] Problem in Finding Hours Duration Between two Dates in Sql Server 2005

问题描述

向所有论坛成员致敬


我在以下过程中无法找到持续时间和净持续时间


我还添加了图像网址，以更好地满足我的要求.

请转到该网址..

http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg? Gv1sRgCM6puf-sutfJ2QE& feat = directlink [ ^ ]


以下是我的T-Sql查询...


选择F.Id作为[FleetID]，Convert(varchar(30)，F.AssignedOn，113)作为[Date]，D.DepotName作为[Depot]，DT.DutyID作为[Duty Number]，
IsNull(Convert(varchar(30)，F.ActualOutSheddingTime，113)，``未分配'')作为[Actual OutShedding Time]，
IsNull(Convert(varchar(30)，F.ActualInsheddingTime，113)，未分配")为[Actual Inshedding Time]，
IsNull(CONVERT(varchar(5)，DATEADD(ms，DATEDIFF(ms，F.ActualOutSheddingTime，F.ActualInsheddingTime)，0)，114)，''00:00'')as [Duration]，
IsNull(DT.RestHours，00)as [ShiftChangeoverTime]，
IsNull(CONVERT(varchar(5)，DATEADD(ms，DATEDIFF(ms，CONVERT(varchar(5)，DATEADD(ms，DATEDIFF(ms，F.ActualOutSheddingTime，F.ActualInsheddingTime)，0)，114)，Convert(Datetime) ，``00:''+ IsNull(Convert(varchar(30)，DT.RestHours，114)，30)))，0)，114)，''00:00'')作为[Net Duration]
从F舰队
F.DutyID = DT.ID上的左外连接关税DT
DT.DepotId = D.ID上​​的内部加入仓库D
V.ID = F.VehicleID的内部加入车辆V



欢迎您提出宝贵的建议.

Vishnu

Hi to All Forum members


I am stuck in finding duration and Net duration in the following procedure


I also adding image Url for better under standing of my requirement.

Please go to this Url ..

http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^]


Following is my T-Sql Query...


Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number],
IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),''Not Assigned'') as [Actual OutShedding Time],
IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,''Not Assigned'') as [Actual Inshedding Time],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,''00:00'')as [Duration],
IsNull(DT.RestHours, 00) as [ShiftChangeoverTime],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, ''00:''+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),''00:00'') as [Net Duration]
From Fleet F
Left Outer Join Duties DT on F.DutyID = DT.ID
Inner Join Depot D on DT.DepotId = D.ID
Inner Join Vehicles V on V.ID = F.VehicleID



Welcome for your valuable suggestions.

Vishnu

推荐答案

如果您将看到的输出提供给我们，将会有所帮助.

It would help if you gave us the output you''re seeing.

这篇关于在Sql Server 2005中查找两个日期之间的小时持续时间的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！