ERROR: Arithmetic overflow error converting float to data type numeric

Question

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a question to solve this problem Handling sql column with mixed varchar and float data types However, now I am getting an error when I try the following code:

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
FROM XLSIMPORT WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

The error I'm getting is:

Msg 8115, Level 16, State 6, Line 41
Arithmetic overflow error converting float to data type numeric.    

Answer 1

The error seems to indicate that the DECIMAL datatype is not defined with the correct precision and scale. The default is DECIMAL(18,0), but something may have happened with your defaults? When I run the query below as you have (just changed to a table variable and added the precision and scale), I am not receiving any error.

DECLARE @test TABLE
([Parcel Number 2(max)] FLOAT)

INSERT INTO @test
        ( [Parcel Number 2(max)] )
VALUES  ( '1.4515020001e+012'  -- Parcel Number 2(max) - float
          )

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL(18,0)) AS NVARCHAR(255))
FROM @test WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

As an FYI, I do get an arithmetic overflow if I try DECIMAL(12,0).