We help IT Professionals succeed at work.

# SQL TIme calculation

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

## View Solution Only

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)'
``````
Commented:
Its easier this way

DATEDIFF(MI ,txtStartTime ,txtEndTime) / 60.0

Commented:
thanks