Unexpected results with double casting numeric datatype in SQL Server

Question

I recently came across a weird case in an ETL process where the results seem unpredictable to me. I read Difference between numeric, float and decimal in SQL Server, but I don't think it's an overflow or decimal precision issue.

Scenario:

Source table "test" in SQL Server 2008 SP3, column a declared as numeric (38,6).

The result is cast first to real, and then to int. The issue doesn't occur if there is a direct cast from numeric to int.

Results of:

SELECT a,CAST(a as real) as real_a,CAST(CAST(a as real) as int) as int_a FROM test;

a: 778881838.810000
real_a: 7.78819E+08
int_a: 778881856

The same experiment, run in SQL Server 2017 (sql fiddle) gives this: http://sqlfiddle.com/#!18/45aca/2

a: 778881838.81
real_a: 778881860
int_a: 778881856

I can (vaguely) understand the ..19E+08 case, but why is there a +18 difference in the double conversion case? The number seems completely arbitrary to me.

Jeroen Mostert · Accepted Answer · 2018-07-10T13:48:55.293

OK, first of all, the result in SQL Server 2017 for real_a is not 778881860. It is 778881856, exactly, just as in SQL Server 2008. How this floating-point value is presented by the client is another matter -- Management Studio shows me 7.788819E+08, sqlcmd produces 7.7888186E+8, and apparently SQL Fiddle uses another library altogether (one I would personally have issue with, seeing as how it obscures significant figures!)

This value is not arbitrary. REAL is a single-precision floating point type that cannot represent 778881838.81 exactly. The closest representable value is 778881856, hence your result (the next lower representable value is 778881792). Without casting to INT, you can see this value using

SELECT STR(CONVERT(REAL, CONVERT(NUMERIC(38, 6), 778881838.810000)), 40, 16)

778881856.0000000000000000

Your use of the term "double" makes me think you're confusing this with FLOAT, which is the double-precision floating point type. FLOAT cannot represent this value exactly either, but it comes much closer:

SELECT STR(CONVERT(FLOAT, CONVERT(NUMERIC(38, 6), 778881838.810000)), 40, 16)

778881838.8099999400000000

Converting this value to an INT yields the (truncated) 778881838. (This truncation is documented and does not happen for conversions to NUMERIC; you'll need to ROUND first before converting if you'd prefer 778881839 instead.)

Perfect. Can you add to your answer how is the representable value calculated so that I can accept it? — pkExec, Jul 10 '18 at 19:48
@pkExec: You mean, how we get from `778881838.810000` to the single-precision float value `778881856` as the closest value? I'd have to dig into the IEEE 754 standard for the details on that -- I can't claim to be an expert. If you enter the number on a site like [this](https://www.h-schmidt.net/FloatConverter/IEEE754.html), you'll see the conversion (and the error). Click the `+` and `-` buttons to move forward/back on the exact values. — Jeroen Mostert, Jul 10 '18 at 19:56

score 1 · Answer 2 · answered Jul 10 '18 at 12:49

Easy example for other people that want to test locally:

DECLARE  @test numeric (38,6)='778881838.810000'
SELECT @test as [Original],CAST(@test as real) as real_a,CAST(CAST(@test as real) as int) as int_a;

Original            real_a          int_a
778881838.810000    7.788819E+08    778881856

You would likely need someone from Microsoft to explain the way it works inside the SQL engine (and certainly to know why they made that decision), but I'll take a stab at the reasoning:

If the output is in scientific notation on the first cast and is then needed to cast to an int, it sets the int to the minimum value that would result in that scientific notation. It ends in 6 instead of 5 because rounding on 5 does not consistently round up on all cases (Alternating tie-breaking for example).

But, no matter the reason, if precision is important, you should explicitly cast to a numeric data type with a defined precision.

score 0 · Answer 3 · answered Jul 10 '18 at 12:49

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

Please find the below links

USE STR Instead of real

STR example Use the below query : -

SELECT a,STR(a ,38,6) as real_a,CAST(CAST(a as real) as int) as int_a FROM test;

Please let me know if you find any issue.

Unexpected results with double casting numeric datatype in SQL Server

3 Answers3