SQL 2016 vs SQL 2008R2 float to numeric Difference:

[ik1972]

Hi,

we are upgrading from SQL server 2008 R2 to SQL server 2016 between our testing we saw this difference. Is there any possibility to get same result. I'm not sure why SQL server giving different result.

declare @float1 float = -318802033560.67, @num numeric(19,6), @dec decimal(19,6)
set @num = @float1
set @dec = @float1
select float_type_without_size = @float1,
cast_to_numeric_type = cast(@float1 as numeric(19,6)),
cast_to_decimal_type = cast(@float1 as decimal(19,6)),
num_from_float = @num,
dec_from_float = @dec

Result SQL 2016 : -318802033560.669983

Result SQL 2008 : -318802033560.669980

Please let me know if its possible to get same result as SQL 2008. Is there any settings in system level or some thing.

Thanks
Inayat

[Ifor]

Interesting.

If the compatability of the default database is SQL2014 or lower then the result is:
-318802033560.669980

otherwise the result is:
-318802033560.669983

It must be something to do with how the inexact nature of floats are handled.

[Ifor]

Just found this:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15

Prior to SQL Server 2016 (13.x), conversion of float values to decimal or numeric is restricted to values of precision 17 digits only. Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.0000000000000000050000000000000005) rounds down to 0. This is no longer a restriction as of SQL Server 2016 (13.x).