Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 converting varchar in to interger

Author  Topic 

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-10-01 : 09:07:56

I have a column which has time recored in it. so for example if some one played football for 2 hours the filed will have 2.00 and if played for 2 and half hrs then 2.30.

column
2.30
1.30
5.00
0.30


so when i use the following

Select sum(convert(int,column)) from table

it throws this following error

Conversion failed when converting the varchar value '0.30' to data type int.

what do i need to do to sum this field up? somebody please help.

thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 09:41:54
Since 2.30 represents two-and-a-half hours, you cannot simply do the summation. Do it like this:
SELECT SUM(60*FLOOR(column) + 100*(column-FLOOR(column)) )
FROM Table
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-10-01 : 09:47:27
hi james K

thank you for your input but it display the wrong total it much to high than the actual total. i got the answer from google which worked. i used

SELECT sum(CONVERT(DECIMAL(15,1),column))
from Table

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 09:57:46
quote:
Originally posted by Muj9

hi james K

thank you for your input but it display the wrong total it much to high than the actual total. i got the answer from google which worked. i used

SELECT sum(CONVERT(DECIMAL(15,1),column))
from Table



The result from my query would be in minutes. To get it in hours, divide that by 60.

If 2.30 represents two hours and 30 minutes, then your query would give the wrong results.
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-10-02 : 08:07:24
i see what you mean, your is correct. Thank you very much, really appreciate it.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 09:51:26
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -