converting varchar in to interger

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

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

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.

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 09:51:26

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources