Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks