Issue with float and currency datatype

prabodh_mishra · November 29th, 2006, 06:22 AM

I've a value, 0.57277044 in my float column. but the select query is returning 0.57277043999999999.
Any idea or solution?

Regards,
Prabodh

Jeff Moden · November 29th, 2006, 12:31 PM

From Books Online...

Approximate Numerics
float

Floating precision number data from -1.79E + 308 through 1.79E + 308.

real

Floating precision number data from -3.40E + 38 through 3.40E + 38.

It always amazes me that folks that use FLOAT to store decimal values never really understand the FLOAT is just what BOL says it is... it is an APPROXIMATION of what a number is. More over, it's a BINARY APPROXIMATION of a number and the default precision is only 7 digits. Sure, the default only takes 4 bytes, but you get what you pay for.

Try this simple test and see what FLOAT does to you even on the most simple of mathematical operations...

Code:

DECLARE @Dividend FLOAT
DECLARE @Divisor  FLOAT
    SET @Dividend = 1
    SET @Divisor  = 10

 SELECT @Dividend/@Divisor AS FloatAnswer

FloatAnswer                                           
----------------------------------------------------- 
0.10000000000000001

(1 row(s) affected)

And why does anyone think they need a number as large as 1.79 * 10^308 ???

If you want accurate answers to money questions, use the MONEY datatype... it even has some neat formatting you can do when converting to VARCHAR like adding commas in the thousands position and right justification for reporting purposes.

If you want accurate answers to other calculations (except for DATETIME which is inherently FLOAT), use DECIMAL.

Change the column data type to something besides FLOAT.

--Jeff Moden

Jeff Mason · November 29th, 2006, 01:37 PM

Quote:

quote:
except for DATETIME which is inherently FLOAT

You sure about that?

The DATETIME datatype in SQL Server is an 8 byte quantity, The first 4 bytes represent the number of days before or after the base date (1/1/1900), and the second 4 bytes represent the time of day as the number of ticks since midnight, where a tick is defined to be 3.33 milliseconds.

The SMALLDATETIME is similar, but with only two bytes for each component, so the date is the days since 1/1/1900 and the time is measured in minutes. (Has anybody ever used this?)

You might be confusing the representation of these values in SQL Server with that of client programming languages which are used to manipulate it.

Of course, the rest of your response about the FLOAT (and REAL) datatype is right on. Unless one is in the business of measuring scientific quantities, the DECIMAL (NUMERIC) datatype should provide all the precision and scale you would need.

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff Moden · November 29th, 2006, 11:53 PM

Very sure...

Code:

DECLARE @DateTime DATETIME
    SET @DateTime = '23:59:59.997'

SELECT CAST(CAST(@DateTime AS FLOAT) AS DATETIME), 
       CAST(CAST(@DateTime AS DECIMAL(38,38)) AS DATETIME)

So far as Scientific calculations go, if you want things only as accurate as interpolation on a fine scale slide rule, you could use FLOAT. I prefer much more accuracy than a slide rule :D Don't wanna crash a Martian Lander or anything like that :)

--Jeff Moden

Jeff Mason · November 30th, 2006, 08:10 AM

With all due respect, your code just proves that a roundtrip CAST of a datetime to DECIMAL is subject to rounding errors during the conversion.

Quoting from BOL:

"Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight."

That having been said, I'm really nitpicking, since knowing the internal format of a datatype isn't very useful - it's the manner in which it is exposed and can be manipulated by client code that matters.

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff Moden · November 30th, 2006, 02:53 PM

Heh... being knit-picky right back...:D

One could say that I never said it was the FLOAT datatype... I quote myself... "except for DATETIME which is inherently FLOAT".

And, if it were treated as two 4 byte integers, you would not be able to add or subtract values less than 1 directly to the date directly without rounding to the nearest whole number occuring.

However, I agree... the bottom line is, whatever type of math you use, if you do anything but FLOAT math to a DATETIME datatype, you stand the chance of an inaccuracy.

--Jeff Moden

prabodh_mishra · December 5th, 2006, 06:27 AM

Thanks for a brain-storming conversation buddies. This has certainly raised my knowledge base.

Cheers,
Prabodh

robprell · December 5th, 2006, 03:46 PM

Float is an inaccurate way of storing data. It changes the value of the data to save space. In this day of cheap storage you should simply just not use a float!!!

Jeff Mason · December 5th, 2006, 04:36 PM

Quote:

quote:Originally posted by robprell
Float is an inaccurate way of storing data. It changes the value of the data to save space. In this day of cheap storage you should simply just not use a float!!!

Actually, the amount of space required to store certain exact numeric values can be significantly more than that required for storing floating point values (e.g. 17 bytes for 38 digits of precision for the numeric datatypes vs 8 bytes for 15 digits of precision for float).

The issue isn't space, but rather how the numeric value is represented. Floating point numbers are stored as a fractional portion and exponent. Floating point numbers can therefore represent extremely large (or small) values (+/- 10^308) but only to 15 digits of accuracy. Exact numerics are stored as scaled integers. These can give you as much as 38 digits of precision and you can specify where the decimal point goes, so you are limited to numbers within the range +/- 10^38 which is considerably smaller than that for floating point numbers.

The real issue is that the floating point representation is in binary and there are many (infinitely many) fractional values of decimal numbers that cannot be represented exactly as a binary fraction. Ten cents (0.10), for example, when converted to a binary fraction is a repeating fraction, so it cannot be accurately presented no matter how many digits you give it.

Floating point numbers are great when representing scientific values which may have very large or very small magnitude, like the distance in miles to the next galaxy, when who cares if we're off a couple digits.

Doesn't work so good when calculating your paycheck, though.

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff Moden · December 6th, 2006, 03:25 AM

Perfect explanation... Nice Job, Jeff.

--Jeff Moden