Add Query

selvi subramanian

14y1.9k

Iam having a table with column name TotalHrs. Its data type is varchar
Time format is HH.MM.SS

I want to sum the column TotalHrs.

TotalHrs Date
00:56:30 AM    5/6/2012
02:08:40 AM       5/6/2012
01:01:00 AM       5/6/2012

and get the o/p like this.

O/P ==> 04:05:10.0000000

How can i get the above result

The query that I had wrote

SELECT SUM (HoursWorked) Total FROM logout where Date='5/6/2012'

I got the error message like this.....

Operand data type varchar is invalid for sum operator....

I need the query

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman14y ago
Accepted answer
This will give you the correct value ---04:06:10.0000000
select convert(char(8),dateadd(second,SUM ( DATEPART(hh,(convert(datetime,HoursWorked,1))) * 3600 + DATEPART(mi, (convert(datetime,HoursWorked,1))) * 60 + DATEPART(ss, (convert(datetime,HoursWorked,1))) ),0),108)
FROM logout where Date='5/6/2012'
selvi subramanian14y ago
thanks to both of them., senthil and santhosh
selvi subramanian14y ago
SELECT SUM (CAST(HoursWorked AS int)) Total FROM logout where Date='4/25/2012' and EmployeeId='Emp1001'

The above query gives the result like this.......

Total 0
Senthilkumar14y ago
Hi,

You need to do the type conversion. What it says the varchar type can't be sum. You can do the some of the int, float, double but not the varchar type.

Either you need to use the CAST or CONVERT.

SELECT SUM (CAST(HoursWorked AS int)) Total FROM logout where Date='5/6/2012'

You can refer this article.
http://msdn.microsoft.com/en-us/library/ms187928.aspx