# sum the Time in Sql RSS

## 3 replies

Last post Feb 02, 2009 01:30 PM by TATWORTH

None

0 Points

8 Posts

### sum the Time in Sql

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

Contributor

2720 Points

593 Posts

### Re: sum the Time in Sql

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.

-George

None

0 Points

8 Posts

### Re: sum the Time in Sql

`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	@SampleSELECT	'25:30' UNION ALLSELECT	'31:30' UNION ALLSELECT	'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`

All-Star

44577 Points

13653 Posts

MVP