The answer to your question depends on the data type you are using to store this value. If you are using the DateTime data type, then this will be easier to do. The idea here is to calculate the number of minutes that have expired since midnight. This
would return an integer. Now that it's an integer, you can do any math you want on it (including sum and division).

If your data type is varchar (or any other string), the code may still work, but you have the potential to get conversion errors if the data in your column is not a valid time.

Declare @Temp Table(TimeValue VarChar(20))
Insert Into @Temp Values('12:45 PM')
Insert Into @Temp Values('1:45 PM')
Insert Into @Temp Values('10:59 AM')
Select Sum(DateDiff(Minute, 0, TimeValue)) As TotalDuration,
Avg(DateDiff(Minute, 0, TimeValue)) As AvgDurationInMinutes,
Sum(DateDiff(Minute, 0, TimeValue))/ 2 As DurationDivideByTwo,
DateAdd(Minute, Avg(DateDiff(Minute, 0, TimeValue)), 0) As AverageDuration
From @Temp

You can copy/paste the code above in to a query window and run it to see how this works.

I got this one in net. But If i divide this result to some value....I can't get the exact time.

Some 4 or 5 min deviation is there in the result.

(I have converted the result to decimal and divide the some value. After that i have converted the decimal value to Time, i got this deviation. How can i fix this.)

DECLARE @Sample TABLE ( data CHAR(5) )

INSERT @Sample SELECT '25:30' UNION ALL SELECT '31:30' UNION ALL SELECT '16:00'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12))) FROM ( SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours, ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes FROM @Sample ) AS d

With SQL 2008, there is an explicit time type that should be much easier to work with for pure times.

Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239

## vijaywebnet

Member

0 Points

8 Posts

## sum the Time in Sql

Jan 26, 2009 04:27 AM|vijaywebnet|LINK

I have a time values in my table..

12:45 PM

1:45 PM

10.59 AM

i like to sum the time in the table...

Also divide the Sum of time (Sum(Time)/2)

Help me.

SQLEXPRESS

## gmmastros

Contributor

3878 Points

594 Posts

## Re: sum the Time in Sql

Jan 26, 2009 12:24 PM|gmmastros|LINK

The answer to your question depends on the data type you are using to store this value. If you are using the DateTime data type, then this will be easier to do. The idea here is to calculate the number of minutes that have expired since midnight. This would return an integer. Now that it's an integer, you can do any math you want on it (including sum and division).

If your data type is varchar (or any other string), the code may still work, but you have the potential to get conversion errors if the data in your column is not a valid time.

You can copy/paste the code above in to a query window and run it to see how this works.

## vijaywebnet

Member

0 Points

8 Posts

## Re: sum the Time in Sql

Jan 27, 2009 04:24 AM|vijaywebnet|LINK

## TATWORTH

All-Star

72415 Points

14018 Posts

MVP

## Re: sum the Time in Sql

Feb 02, 2009 01:30 PM|TATWORTH|LINK

Which version of SQL Server are you using?

With SQL 2008, there is an explicit time type that should be much easier to work with for pure times.

This earns you a point and marks your thread as Resolved so we will all know you have been helped.

FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239