SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 converting varchar in to interger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 10/01/2013 :  09:07:56  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/01/2013 :  09:41:54  Show Profile  Reply with Quote
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
Starting Member

United Kingdom
49 Posts

Posted - 10/01/2013 :  09:47:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/01/2013 :  09:57:46  Show Profile  Reply with Quote
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
Starting Member

United Kingdom
49 Posts

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

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/02/2013 :  09:51:26  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000