SQL Server Arithmetic Overflow Error – How to Fix Float to Numeric Conversion

castsql-server-2012t-sqltype conversion

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.

Best Answer

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).

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

SQL Server Arithmetic Overflow Error – How to Resolve Varchar to Numeric Issues

You get the error because essentially at the end you're dividing an 8 character long string by a numeric value which is has precision as 3 like this example below

DECLARE @num VARCHAR(8) = '12356456'
DECLARE @den DECIMAL(3,0) = 121

SELECT @num / @den

SQL Server internally tries to convert VARCHAR(8) to DECIMAL(3,0) and fails with your error.

CAST([Column2] as numeric(8)) works because now VARCHAR(8) can be converted to numeric(8).

You don't really need to convert DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()) to VARCHAR(8)

Best Answer

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

SQL Server Arithmetic Overflow Error – How to Resolve Varchar to Numeric Issues

Related Question