asked on

float (8) data type problem in MS SQL Server table

Hey,

I am new on the MS SQL Server. There is a problem really bother me. In one of my table, the data type for one column field is assigned to float (8). When I run the Query analyzer, or run the SQL statement through programing (ex. C++), the recordset coming back has been added many zero ramdomly.

For example, if the real data is 25.212 in the table, the recordset I got (or through Query Analyzer) is 25.212000000000001.

Is any guru know how to solve the problem??

Thanks.

rafrancisco

You can cast that float value to decimal:

SELECT CAST(YourField AS DECIMAL(10, 3))
FROM YourTable

This will return a decimal value with 3 decimal places.

suwanee

ASKER

Thanks. But the data inside the table may not have 3 decimal point. What I want is returning all the raw data without changing anything.
For example:
25.212 -> 25.212
25.2128 -> 25.2128
25.2 -> 25.2

Thanks again for your answer.

rafrancisco

As far as I know, that's how SQL Server represents floats, with a lot of decimal places.

select cast(123.49 as float)

Try this one and instead of getting 123.49, you will get 123.48999999999999. You really have to cast it to the maximum number of characters you want.

Another option is to cast it to varchar.

SELECT CAST(YourField AS VARCHAR(50))
FROM YourTable

You will get your desired number of decimal places with this one.

Scott Pletcher

Alter the data type of the table column from FLOAT to DECIMAL(). This will allow you to store exactly what you input. FLOAT *cannot* -- it only stores *approximate* values, not exact ones. If you need *exact*/*specific* values, use DECIMAL().

suwanee

ASKER

It even worse. After I change the data type to decimal(9), it will trunk my data to integer: 25.213 -> 25

Thanks.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial