?
Solved

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

Posted on 2005-04-20
9
Medium Priority
?
403 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:suwanee
  • 2
  • 2
  • 2
6 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13828830
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.
0
 

Author Comment

by:suwanee
ID: 13828881
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.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13828967
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13829041
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().
0
 

Author Comment

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

Thanks.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 13829210
Try DECIMAL(12, 3)

I think it defaults to 0 decimal places if you don't specify differently.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question