1

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.    
  • Are you certain that Excel has actually converted the number? If you don't set a datatype for a cell & paste a long number in, it'll display it as 1.xe+nnn, but the real value is still there (format cells->category->number) – Philᵀᴹ Jun 29 '16 at 7:02
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.