Why does DECIMAL behave like FLOAT?

Question

Even though DECIMAL is an exact numeric type (unlike FLOAT, which is approximate), it behaves rather strangely in the following example:

DECLARE @DECIMAL_VALUE1 DECIMAL(20,9) = 504.70 / 0.151562
DECLARE @DECIMAL_VALUE2 DECIMAL(20,0) = 504.70 / 0.151562
DECLARE @INTEGER_VALUE INT            = 504.70 / 0.151562

SELECT
    @DECIMAL_VALUE1 AS DECIMAL_VALUE1,  -- 3329.990366978
    @DECIMAL_VALUE2 AS DECIMAL_VALUE2,  -- 3330
    @INTEGER_VALUE  AS INTEGER_VALUE    -- 3329

A value other than 3329 causes a bug in our application. Making the variable type an INTEGER solved our issue, but I cannot get my head around as to why it was caused in the first place.

decimal is NOT an exact numeric type. Sure it has higher precision and range....you should NEVER rely on comparing non integers unless you use an epsilon or delta: https://stackoverflow.com/questions/3420812/how-do-i-find-if-two-variables-are-approximately-equals/24671992 Do you have an XY problem? — Mitch Wheat, Oct 17 '18 at 12:29
The exact result of that calculation is 3329.990366978..., what exactly are you asking? — DavidG, Oct 17 '18 at 12:29
`DECIMAL` is a base 10 floating point type, unlike `FLOAT`, which is a base 2 floating point type. It's *still* floating point, though, and you can *still* have rounding issues if you exceed the number of decimals supported. The result of `504.70 / 0.151562` cannot be represented exactly in a finite number of base 10 digits. It's not `3329` or `3330`, in any case (`3330 * 0.151562 = 504.70146`, exactly). — Jeroen Mostert, Oct 17 '18 at 12:36
Perhaps what you are looking for is `FLOOR(504.70 / 0.151562)` but without some more clarity, it's not possible to answer. — DavidG, Oct 17 '18 at 12:39

xQbert · Accepted Answer · 2018-10-17T18:33:24.177

You asked, "Why it was caused in the first place":

To know why you need to understand the nature of each datatype and how it operates within SQL Server.

Integer math truncates decimals (no rounding, same as "FLOOR" function (which is why you get 3329)).
Decimal with 0 places rounds (which is why you get 3330 as 3329.99 rounds up)
Decimal with precision/scale rounds to Nth scale (which is why you get 3329.990366978...).

So this isn't unexpected behavior, it's expected given the datatypes involved. It just may have been unanticipated behavior. The nuances of each datatype can be problematic until one runs into them.

I'll choose to ignore the float comment as it is not germane to the question.

Why does DECIMAL behave like FLOAT?

1 Answers1