Sunday, September 04, 2011

Calculating total of all MM:SS

Yesterday in a forum I found a question asking for a way to calculate the total of MM:SS in SQL Server. More over that column is being maintained as a "Varchar" type!!

Below is the query i suggested as my suggestion. Please note, the below query would work as long as Minutes (MM) is lesser than 60.

--Sample table structure
CREATE TABLE SumTimeTest
(
id varchar(4),
timeValue varchar(5)
)
GO

--Test data
INSERT INTO SumTimeTest VALUES('1','30:50')
INSERT INTO SumTimeTest VALUES('1','30:25')
INSERT INTO SumTimeTest VALUES('2','20:50')
INSERT INTO SumTimeTest VALUES('3','25:20')
INSERT INTO SumTimeTest VALUES('1','35:50')
GO

Solution:

SELECT CONVERT(VARCHAR(10),
DATEADD(ss,SUM(
DATEDIFF(second,0,
DATEADD(DAY,0,CAST('00:' + timevalue AS DATETIME)))),0)
,108) AS [HH:MM:SS]
FROM sumtimetest

No comments: