Disabled the automatic round of SQL SERVER 2000 on the float data type

Hello,

I have a problem with the round of SQL Server 2K on the float data type.

In my application I do a SQL request for find a row in a table of SQL Server 2K.

This is the request :

SELECT DISTINCT N_ROW_ID FROM COMM WHERE N_ARTICLE_ID=79510 AND N_DATASOURCE_ID=1 AND N_SOURCE_ID=-102 AND N_PROVIDER_ID=-100
AND N_LEAD_TIME IS NULL AND N_UNIT_PRICE = 329.78 AND N_UNIT_PRICE_CURRENCY_ID=1

N_UNIT_PRICE is a float data type.

In the line need, the field N_UNIT_PRICE has 329.78 but with the round of SQL Server 2K I have 329.779999999999997.

So I never find the row

Do you have a solution for comparate a float flied with SQL Server round and a float send in a request as my 329.78?

I can't transform the column in decimal or numeric data type.

Is it possible to Disable the function?

Regards

Floats are known as inexact or approximate datatypes. Due to the 'fuzzy' rounding, the same value may display slightly different every time you look at it.

Floats are not appropriate for any situation where you will have to search or index the value.

If your N_UNIT_PRICE is a float, you 'could' convert it to a decimal in the query. For example, this may work:

Code Snippet

SELECT DISTINCT
N_ROW_ID
FROM COMM
WHERE ( N_ARTICLE_ID = 79510
AND N_DATASOURCE_ID = 1
AND N_SOURCE_ID = -102
AND N_PROVIDER_ID = -100
AND N_LEAD_TIME IS NULL
AND cast( N_UNIT_PRICE AS decimal(10,2)) = 329.78
AND N_UNIT_PRICE_CURRENCY_ID = 1
)

|||As Arnie described this is no "function" as you mentioned, float datatypes only provide exact precision as you defined it on the column. The precision "floats" on the size of the value.

Jens K. Suessmeyer.

http://www.sqlserver2005.de