Geek asks and answers

Sum of time in sql

I have a table named tblAttendanceDailySummary, and there is a datatype field in time called timeAttendanceHour (e.g. 09:44:25). I want to summarize this field at the end of the month. I mean that one employee needs one month of working time. How can I summarize the time data type.

+3
source share
2 answers

The time data type has a range from 00: 00: 00.0000000 to 23: 59: 59.9999999, so you cannot use the time. You should calculate the hour, minute and second instead.

Something like this might work for you

select H.Val, M.Val, S.Val
from (
       --Your query goes here
       select dateadd(second, sum(datediff(second, 0, timeAttendanceHour)), 0) as sumtime
       from YourTable
     ) as T
  cross apply (select datedifF(hour, 0, T.sumTime)) as H(Val)
  cross apply (select datediff(minute, 0, dateadd(hour, -H.Val, T.sumTime))) as M(Val)
  cross apply (select datediff(second, 0, dateadd(hour, -H.Val, dateadd(minute, -M.Val, T.sumTime)))) as S(Val)

If you want to get the result as HH: MM: SS, you can use this:

select cast(H.Val as varchar(10))+':'+right(M.Val+100, 2)+':'+right(S.Val+100, 2)
+4
source

:

( (timeAttendanceHour))/60 +
    sum ( (timeAttendanceHour)) +
    sum (hour (timeAttendance)) * 60 MonthlyMinutes,
intMonthID
tblAttendanceDailySummary
intYear = 2012
group by intEmployeeID, intMonthID

0

More articles:


All Articles