Calculate the Sum of the Column Which Has Time Datatype:

Calculate the SUM of the Column which has Time DataType:

You could sum the total number of seconds, or datediff(second,0,datecolumn). You can format that as a time string with some math. For example, the total number of minutes is totalseconds / 60 % 60. For example:

select  cast(sum(datediff(second,0,dt))/3600 as varchar(12)) + ':' + 
        right('0' + cast(sum(datediff(second,0,dt))/60%60 as varchar(2)),2) +
        ':' + right('0' + cast(sum(datediff(second,0,dt))%60 as varchar(2)),2)
from    TestTable

Working code at SQL Fiddle.

sum of time datatype in sql

The time data type has a range of 00:00:00.0000000 through 23:59:59.9999999 so you can't cast to time. You have to calculate the hour, minute and second instead.

Something like this could 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 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)

Calculate the sum of time datatype in sql

Are you sure Total working time is given as Time datatype?
Your sample input is '32:23:00' which is not supported by 'time' Data type
Because its range is 00:00:00.0000000 through 23:59:59.9999999.

If it is Time datatype, then you can calculate the salary as juergen states:

select cast(cast (100.00 as money) * datediff(second, 0, '23:23:00') / (60.0 * 60.0) as money)

I cast the values into money data type to show that, its possible to multiply it with datediff() functions returned value.

Calculating SUM() of time values returns the time value as a string

You cannot SUM the time value. But you can convert TIME_TO_SEC, calculate sum of seconds, then convert SEC_TO_TIME:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(total)))
FROM h
WHERE ...
-- should give you something similar to 02:24:00

Query to get sum of a column based on the date of it's created datetime?

use a cast(createdAt as date) to convert the datetime to date

select sum(caloriesConsumed), cast(createdAt as date)
    from record
    group by cast(createdAt as date)

SUM of time returns date part as well

This guy wanted to calculate the sum of the field which has the time datatype.
The Answer on this post had a proper solution which may be helpful:

select  cast(sum(datediff(second,0,dt))/3600 as varchar(12)) + ':' + 
right('0' + cast(sum(datediff(second,0,dt))/60%60 as varchar(2)),2) +
':' + right('0' + cast(sum(datediff(second,0,dt))%60 as varchar(2)),2)
from    TestTable

Reference : Calculate the SUM of the Column which has Time DataType:

---

Related Topics