SQL 2016 vs SQL 2008R2 float to numeric Difference:

Question

SQL 2016 vs SQL 2008R2 float to numeric Difference:

inayatkhan

SSC Eights!

Points: 844
More actions
August 4, 2020 at 6:11 pm

#3777289

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Your SELECT includes 5 items, yet your Result section includes only one (per instance). Can you change your result to match the query (or vice versa)?

Also, are you aware that FLOAT is not an exact datatype? I never use it, for that reason.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

inayatkhan SSC Eights! Points: 844 More actions · Answer 2

For all its same result for that reason I just give me and in query i just want to show i try with these options. As we are upgrading from SQL 2008 to 2016 so we don't have option to change the column data type.

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

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. This is no longer a restriction as of SQL Server 2016 (13.x).

Your result is 18 digits long