String to Float type conversion in xxx.xx format

[AlbertKim247]

I'm trying to populate a field of datatype float, with cost data from a varchar(30) field. The cost data is a string that looks like "999.99". When I select this cost data and insert into a float field, the resulting value in the float field looks like "999.99000000000001".

I think this issue has to do with implicit conversions, but I'm trying to find a workaround. Is there a way to insert a value like "999.99" into the float field as "999.99"?

Here are a couple similar examples using the Northwind database in SQL Server 2000 where I'd like to see the results look like "999.99", but they don't.

select top 10 cast('999.99' as float) from orders

select top 10 convert(float (1), '999.99') from orders

 

[bborissov]

And why not:

select top 10 cast('999.99' as numeric(10,2)) from orders

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

don't use floats; it is an approximate number data type

use numeric or decimal to store monetary values

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[AlbertKim247]

I understand that float is not the appropriate datatype, but unfortunately, I am inserting this data into a field of datatype float.

The numeric datatype seems to work in the SELECT statement, but when I populate a float field with that value, it still appears like "999.99000000000001".


Any other ideas?

 

[SQLDenis]

that is what a float looks like

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[AlbertKim247]

Do you mean, that there is no way for it to show up as 999.99 or even 999.990000000000?

 

[Catadmin]

Nope. Not really. That 1 comes across in the conversion and since float accounts for fractional values, it's going to stick it in there regardless of what you try.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"