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:
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.