15,663,728 members
See more:
I have timespan values stored in SQL server like 165:56,258:65,258:50.

I need sum of those values like 683:51

What I have tried:

SQL
```WITH CTE_SUM AS (
SELECT
SUM(
LEFT([tot.time], 2) * 3600 + SUBSTRING([tot.time], 4, 2) * 60
) AS SUM_AS_SECONDS
FROM
[dbo].[production dt]
WHERE
[ordernumber] = '800000025277'
AND setup = 'I'
AND machine IN('HMC-01', 'HMC-02')
)
SELECT
isnull(
CAST(SUM_AS_SECONDS / 3600 AS VARCHAR) + ':' + CAST(SUM_AS_SECONDS % 3600 / 60 AS VARCHAR),
'00:00'
)
FROM
CTE_SUM```
Posted
Updated 9-Jan-23 22:44pm
v3

## Solution 2

Storing time-spans as strings is always a bad idea. Ideally, you should be storing them as a numeric value - for example, the total number of minutes.

If you're going to use strings, then you need to use them properly. Your first value is `165:56`, so its clear that taking the first two characters as the number of hours isn't going to work.

You need to find the index of the `:` character, and split the string on that:
SQL
```WITH cteTimes As
(
SELECT
SUM(
CAST(LEFT([tot.time], CHARINDEX(':', [tot.time]) - 1) As int) * 3600
+ CAST(SUBSTRING([tot.time], CHARINDEX(':', [tot.time]) + 1, LEN([tot.time])) As int) * 60
) As TotalTimeInSeconds
FROM
dbo.[production dt]
WHERE
ordernumber = '800000025277'
AND
setup = 'I'
AND
maching In ('HMC-01', 'HMC-02')
)
SELECT
CAST(TotalTimeInSeconds / 3600 As varchar(20)) + ':' + CAST((TotalTimeInSeconds % 3600) / 60 As varchar(20))
FROM
cteTimes
;```
NB: You have given your table and columns names which include "special" characters - "tot.time", "production dt" - which forces you to wrap them in square brackets in your queries. You should avoid this, and only use standard characters (A-Z, a-z, 0-9, _) in the names.

## Solution 1

The most obvious solution is simple: don't store numeric values as strings - convert them to a "sensible" numeric value. For example, if the difference is usually measured in minutes and seconds, convert the timespans to seconds and store that in a INT field.
That way, you can do math on them directly without any fannying about with string chopping and conversion which gets incredibly slow on a large DB since SQL string handling is poor at best. And it means that the values stored are always valid instead of potentially corrupt.

I have no idea what intervals you samples are: they aren't minutes and seconds, or hours and minutes, they aren't formatted right for a "standard number" - so it could be anything, including invalid values already in your DB!

CPallini 10-Jan-23 4:44am
5.
Richard Deeming 10-Jan-23 4:47am
Treating it as hours and minutes gives the desired total, even though `258:65` should really be `259:05`. :)
OriginalGriff 10-Jan-23 4:58am
That's what I mean - already his DB has crap in it! :laugh: