CODEWITHSUNDEEP
asp.netsqlsql-server
Joe Yan
Joe Yan

Reputation: 2095

ASP - How to calculate total hour and minutes within time range

I need to prepare a yearly management report to show the total overtime (OT) work hours and minutes of all staff within time range and month.

The time format in ms sql 2000 database is as follows:

Each record will contains the FROM date & time and TO date & time

The report layout is as follows:

I had no idea how to divide and calculate the total hours & minutes within the time range as each OT records will overlap several time range and date.

Please help. Thanks.

Joe

Upvotes: 1

Views: 1547

Answers (1)

Sparky
Sparky

Reputation: 15085

The SQL DateDiff function can be used to compute the number of minutes, i.e.

declare @fromDT datetime
declare @toDT datetime
set @fromDT = '10/22/2011 18:30'
set @toDT = '10/22/2011 22:45'

select @fromDT,@toDT,DATEDIFF(mi,@fromDt,@toDt),
    ltrim(str(DATEDIFF(mi,@fromDt,@toDt)/60))+':'+
    ltrim(str(DATEDIFF(mi,@fromDt,@toDt)%60)) as HoursMin

Returns

StartTime                   End Time            Mins    HoursMin
2011-10-22 18:30:00.000 2011-10-22 22:45:00.000 255     4:15

Upvotes: 1

Related Questions