Float datataype field in MS SQL?

Question

I have a table with a float datatype. I create a record, and set the value of the field to .06, but the database stores it as 5.9999999999999998E-2. If I assign the value 1.06, it is stored as 1.0600000000000001. If I assign the value 110.06 to the field, it is stored as 110.06. How can I force the databse to retain only the precision I assign the field in my SQL, without modifying the schema, so that even small amounts like .06 are stored to two decimal points?

I am not porting this to MySQL, when I say "my SQL," I am refering to my SQL statement.

As I said - changing the datatype is NOT an option.

Answer 1

The previous answers were correct; you want to use the DECIMAL data type in SQL Server. Alternatively, you can use NUMERIC.

Both those types take a two-part argument: A precision and a scale. The precision is the total number of digits allowed, including decimal places; the scale is the number of decimal places you will want; in your case, 2.

So, DECIMAL(10,2) or NUMERIC(10,2) would allow any number from 0.00 to 99999999.99.

ALTER TABLE tablename
ALTER COLUMN columnname
DECIMAL(10,2)

UPDATE:

If changing the data type is not an option, the best you can do is round your results in select statements.

SELECT ROUND(column, 2) FROM table

http://msdn2.microsoft.com/en-us/library/ms175003.aspx

PS: Try not to be so curt. We're trying to help you; copping an attitiude isn't the appropriate response when you are asking for favors.

Answer 2

If you can't change the data type for the column, you can't change how it is stored by the database manager. Float values are stored using the IEEE standard for floating point numbers, which has this behavior (not really a bug, though it is bugging you).

As a workaround, try using a CAST when you select from the table, for example

select cast(float_column as decimal(4,2)) from table

The DBMS will round the number, and you'll get the output you want.

For all the ugly details on floating point numbers, see the Wikipedia article below.

Answer 3

Create Table Float

Answer 4

As the other poster stated, use Decimal. The 2 fields you need to be aware of are Precision and Scale.

Precision specifies the number of digits to the left of the decimal and Scale is the number of digits to the right of the decimal. It has its limitations in this respect since it helps to know your max values ahead of time.

Answer 5

http://www.databasejournal.com/features/mssql/article.phpr/2212141

FLOAT does rounding. Use DECIMAL.

Answer 6

You have to use a program to encrypt and it will be interesting.