Add Query

Question

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

Santhosh Kumar Jayaraman · Answer

This will give you the correct value ---04:06:10.0000000 select convert(char(8)dateadd(secondSUM ( DATEPART(hh(convert(datetimeHoursWorked1))) * 3600 + DATEPART(mi (convert(datetimeHoursWorked1))) * 60 + DATEPART(ss (convert(datetimeHoursWorked1))) )0)108) FROM logout where Date='5/6/2012'

selvi subramanian · Answer

thanks to both of them. senthil and santhosh

selvi subramanian · Answer

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

Senthilkumar · Answer

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