Don't use a string column to store a time value - use
the time
type[
^] instead.
The "conversion failed" error suggests that your column contains a value which is
not a valid time value, or which contains leading white-space. Both example values you've provided (
15:10
and
06:30
) can be correctly converted to the
time
type.
However, you can't use the
SUM
aggregate with the
time
type. Instead, you'll need to sum the total minutes represented by each instance:
DECLARE @tb TABLE (Opt time);
INSERT INTO @tb (Opt)
VALUES('15:10'), ('06:30'), ('16:07');
SELECT
Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],
Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60)
+ ' hours '
+ Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60)
+ ' minutes'
As [Description]
FROM
@tb
;