Question

I have column x in hh:mm format of datatype varchar in SQL Server and I want to perform sum on that x column.

I created a user-defined function to convert total min into hh:mm format.

Then I tried to perform sum to calculate total duration:

sum(cast(new_totalmin AS Int)) 

4. also i want total of HH:mm exactly as example 4:20 +1:10

---

5:30 5 hour: 30 minute

5. or i can do one thing here insted hh:mm i keep column as it is which is totalmin as int once sum cal insted of hh:mm (hh.mm which is in decimal also ok for me PSB it will be ok for me ':' or '.' format ) (60 min --> 1:00 --> 1.00

         90 min -->  1:30   -->1.30
        ---------------------------------
   

   sum --> 150 min -->2:30 --> 2.30)

but it did not work.

I got an error like

Conversion failed when converting the varchar value '01:00' to data type int

question from:https://stackoverflow.com/questions/65857237/i-have-column-x-in-hhmm-format-of-datatype-varchar-in-sql-server-and-i-want-to

Answer 1

DECLARE @SampleData AS TABLE (HourMinutes VARCHAR(10));

INSERT INTO @SampleData VALUES ('4:32');
INSERT INTO @SampleData VALUES ('5:28');
INSERT INTO @SampleData VALUES ('6:00');
INSERT INTO @SampleData VALUES ('7:10');

SELECT * FROM @SampleData

SELECT SUM(datediff(minute, 0, HourMinutes)) TotalMinute 
FROM @SampleData

You will get following output