Post reply

SQL 2014 vs 2017 Float to Numeric conversion

  • June 13, 2018 at 1:57 pm

    #406686

    Hi,

    When I run query below in SQL2014 SP2 (Windows Server 2012 Standard) and SQL2017 CU5 (Windows Server 2016 Standard), they give me different values. Is this behavior expected? Thanks

    select convert(numeric(38,12),cast('1234567891230000000' as float)/1000000000)
    Output of SQL2014 SP2: 1234567891.229999800000
    Output of SQL2017 CU5: 1234567891.229999780655

    select convert(numeric(38,12),cast('1234567891240000000' as float)/1000000000)
    Output of SQL2014 SP2: 1234567891.240000000000
    Output of SQL2017 CU5: 1234567891.240000009537

  • June 13, 2018 at 3:59 pm

    #1993921

    The behavior changed in SQL 2016:
    https://support.microsoft.com/en-us/help/4010261/sql-server-and-azure-sql-database-improvements-in-handling-some-data-t

    If you run the query on a SQL 2017 database set to compatibility level 120 (SQL 2014) or less, it will produce the same output as when run on SQL 2014:
    CREATE DATABASE Testr
    GO

    USE Testr
    GO

    ALTER DATABASE Testr SET COMPATIBILITY_LEVEL = 130 WITH ROLLBACK IMMEDIATE
    GO

    SELECT convert(numeric(38,12),cast('1234567891230000000' AS float)/1000000000)
    -- 1234567891.229999780655 
    GO

    ALTER DATABASE Testr SET COMPATIBILITY_LEVEL = 120 WITH ROLLBACK IMMEDIATE
    GO

    SELECT convert(numeric(38,12),cast('1234567891230000000' AS float)/1000000000)
    -- 1234567891.229999800000
    GO

    Eddie Wuerch
    MCM: SQL

  • June 14, 2018 at 7:33 am

    #1993994

    Thank you so much.
    I found lines below in the link that you shared:

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

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