sql server programming: I want to create the stored procedure for sum up a column, the column is a
varchar field, the format is [hh:mm:ss].
the sum up function is: calculate the total of the duration, for example:

column
===========
01:04:03
03:59:03

I need to get the final result as 05:03:06
my plan is convert the hour (hh), minis(mm) into seconds for each record,
sum up the seconds and convert into [hh:mm:ss] again.

You can try:

SELECT
RIGHT('0' + CONVERT(VARCHAR(2), seconds / 3600), 2) + ':' +
RIGHT('0' + CONVERT(VARCHAR(2), (seconds % 3600) / 60), 2) + ':' +
RIGHT('0' + CONVERT(VARCHAR(2), (seconds % 3600) % 60), 2)
FROM
(SELECT SUM(
CONVERT(INT, SUBSTRING(YourColumn, 1, 2)) * 3600 +
CONVERT(INT, SUBSTRING(YourColumn, 4, 2)) * 60 +
CONVERT(INT, SUBSTRING(YourColumn, 7, 2)))
FROM YourTable
) x (seconds)

.... I highly recommend that in the future, you store durations as integers,
at the highest granularity of the data. For instance, in this case you have
the data in seconds (hours * 3600, minutes * 60) , so you should really just
store an integer in seconds and worry about display on the client side.
That would have made this sum a lot easier to calculate.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


[quoted text, click to view]

select
convert(varchar(8),convert(datetime,sum(convert(float,convert(datetime,col)))),108) from tbl

if you want to allow for more than 24 hrs total (but you might want to use a
datetime instead)

select convert(varchar(10),datediff(hh,'19000101',d)) +
right(convert(varchar(8),d, 108), 6)
from (
select d = convert(datetime,sum(convert(float,convert(datetime,col)))) from
tbl
) a

Here's another option if both the input and the result is less than 24
hours:

create table t1(c1 varchar(8))
insert into t1 values('01:04:03')
insert into t1 values('03:59:03')

select convert(varchar(8), dateadd(s, sum(datediff(s, '00:00', c1)),
'00:00'), 108)
from t1

--
BG, SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]