We help IT Professionals succeed at work.

SQL TIme calculation

rlking12
rlking12 asked
on
The calculation works great until you span 2 days.

Such as Oct 28 12pm to 1:30am Oct 29.

Should be 13.5 hours, my statement is returning 14.3

Any help would be greatly appreciated.



 Select Tech_ServiceID,txtTech,txtStartTime,
                txtEndTime,TimeDate,dtdate,
                convert(real,(24 + DateDiff(ss,txtStartTime,txtEndTime) / 3600)%24) +
                convert(real,((60 + (sum(DateDiff(ss,txtStartTime,txtEndTime)) % 3600) / 60) % 60) * .01) as Total_Time,
                Tech_Service_PayCodes.PayCodeDescription as txtPayCode
                from Tech_Service_Time
                LEFT OUTER JOIN Tech_Service_PayCodes ON Tech_Service_Time.txtPayCode = Tech_Service_PayCodes.PayCodeID
                        where timedate = '10-28-11'                
                group by txtTech,Tech_ServiceID,txtStartTime,
                txtEndTime,TimeDate,dtdate,Tech_Service_PayCodes.PayCodeDescription
                order by TimeDate,txtStartTime


THanks......
Comment
Watch Question

Commented:
I think you have your bracketing all mixed up and have gotten too complicated. Why do you have a sum in one part of the calculation and not the other?

If you only want the result as a number of hours then all you need is:

 DateDiff(ss,,txtStartTime,txtEndTime) / 3600.0


Commented:
An example to play with:
declare @d1 datetime
declare @d2 datetime

select @d1 = '2011-01-01 12:00'
select @d2 = '2011-01-02 12:00'

select @d1, @d2


 select 
 DateDiff(ss,@d1,@d2) / 3600 as 'hours',
 DateDiff(ss,@d1,@d2) % 3600 as 'sec remainder',
 DateDiff(ss,@d1,@d2) / 3600.0 as 'hours (real)'

Open in new window

Its easier this way

DATEDIFF(MI ,txtStartTime ,txtEndTime) / 60.0

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.