SQL 2016 vs SQL 2008R2 float to numeric Difference:

  • inayatkhan

    Say Hey Kid

    Points: 671

    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

  • Phil Parkin

    SSC Guru

    Points: 244733

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • inayatkhan

    Say Hey Kid

    Points: 671

     

    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: 17334

    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

     

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

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