Question: I have a table In MS SQL-Server which has hours in a `varchar` column. For ex. `'4:30'` (hr:Min). Basically it is a time span for an amount of work. Now I need the total sum of this column. How to convert it ?

My table looks like this

```
Create table tblWrkingHors
(
    Id int primary key,
    name varchar(50),
    WorkingHr varchar(8)
)

```

My table data is like this

```
==============================================================
Id       name       WorkingHr 
1        sam         08:20
2        sam         04:50
3        sam         04:34
4        Pam         05:50
5        sam         04:30
6        Pam         06:40
7        Pam         04:50
8       Todd         06:10
9       Todd         05:50
10      Todd         08:50   

```

Answer: 
[SQL Fiddle](http://sqlfiddle.com/#!6/2ec56/2)

**MS SQL Server 2012 Schema Setup**:

```
CREATE TABLE Test_Table(Id INT, name VARCHAR(10), WorkingHr VARCHAR(8))
INSERT INTO Test_Table VALUES
(1        ,'sam' ,'08:20'),
(2        ,'sam' ,'04:50'),
(3        ,'sam' ,'04:34'),
(4        ,'Pam' ,'05:50'),
(5        ,'sam' ,'04:30'),
(6        ,'Pam' ,'06:40'),
(7        ,'Pam' ,'04:50'),
(8       ,'Todd' ,'06:10'),
(9       ,'Todd' ,'05:50'),
(10      ,'Todd' ,'08:50')

```

**Query 1**:

```
SELECT name
      ,CAST(DATEADD(MILLISECOND, 
               SUM(DATEDIFF(MILLISECOND, '00:00:00.000'
         , CAST(WorkingHr AS TIME))), '00:00:00.000') AS TIME) AS Total_Time
FROM Test_Table
GROUP BY name

```

**[Results](http://sqlfiddle.com/#!6/2ec56/2/0)**:

```
| NAME |       TOTAL_TIME |
|------|------------------|
|  Pam | 17:20:00.0000000 |
|  sam | 22:14:00.0000000 |
| Todd | 20:50:00.0000000 |

```
Convert the times to a number of minutes before summing 

```
SELECT
   SUM( 60 * CONVERT(int, LEFT( WorkingHr, 2)) +
             CONVERT(int, RIGHT(WorkingHr, 2))   ) AS TotalMinutes
FROM tblWorkingHours;

```

Or if you need the sum per person:

```
SELECT
   name,
   SUM( 60 * CONVERT(int, LEFT( WorkingHr, 2)) +
             CONVERT(int, RIGHT(WorkingHr, 2))   ) AS TotalMinutes
FROM tblWorkingHours
GROUP BY name;

```

However a `varchar` data type is probably not the best option. Store the time spans in minutes as `int`. It's the applications (front-end) responsibility to convert and display the data in an appropriate format, not the database's responsibility. Therefore choose a data type best suited for calculations, not for display.