SQL 2016 vs SQL 2008R2 float to numeric Difference:


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.



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

otherwise the result is:

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

Just found this: