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
, 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
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
ordernumber = '800000025277'
setup = 'I'
maching In ('HMC-01', 'HMC-02')
CAST(TotalTimeInSeconds / 3600 As varchar(20)) + ':' + CAST((TotalTimeInSeconds % 3600) / 60 As varchar(20))
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.