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: 
A VARCHAR may not be the most efficient version of storing time, but with a bit of casting you can do what you're looking for;

```
WITH cte AS (
  SELECT name, SUM(DATEDIFF(MINUTE, '00:00', CAST(WorkingHr AS TIME))) t
  FROM mytable GROUP BY name
)
SELECT name, RTRIM(t/60) + ':' + RIGHT('0' + RTRIM(t%60), 2) FROM cte

```

[An SQLfiddle to test with](http://sqlfiddle.com/#!6/82ba8/1).

What it basically does is to cast the string to a TIME, then using DATEDIFF to convert the time to minutes. It then just basically sums up the minutes per name.

The outer query just converts minutes to a HH:MM string using simple string operations.

If you're not looking to group by name, the query can be simplified to;

```
WITH cte AS (
  SELECT SUM(DATEDIFF(MINUTE, '00:00', CAST(WorkingHr AS TIME))) t FROM mytable
)
SELECT RTRIM(t/60) + ':' + RIGHT('0' + RTRIM(t%60), 2) FROM cte;

```

...which is pretty much the same query without grouping.
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.