Last post Feb 02, 2009 02:30 PM by TATWORTH
Jan 26, 2009 05:27 AM|vijaywebnet|LINK
I have a time values in my table..
i like to sum the time in the table...
Also divide the Sum of time (Sum(Time)/2)
Jan 26, 2009 01: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.
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
You can copy/paste the code above in to a query window and run it to see how this works.
Jan 27, 2009 05:24 AM|vijaywebnet|LINK
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
Feb 02, 2009 02: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.