Cast/Convert Arithmetic Overflow Why?-CodePudding

This works

select top(1786519) CONVERT(Decimal(8,2), Cost) 
from table

This does not

select top(1786520) CONVERT(Decimal(8,2), Cost) 
from table

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

Is there significance in this many rows that causes the overflow error?

CodePudding user response：

You could use try_convert to find which row(s) contain data that cannot cast to the target data type, for example,

with x as (
  select Convert(float, 123456.78) cost union all
  select Convert(float, 1234567.789)  /* too large for decimal (8,2) */
)
select *
from x
where try_convert(Decimal(8,2), Cost) is null;

CodePudding user response：

FLOAT in SQL is painful to work with converting to VARCHAR and measuring the length won't work correctly. Since you don't have TRY_CAST, you have to "guess" if SQL can convert it. I think by converting to a larger decimal and checking the length, you should be able to identify the numbers that don't fit into your DECIMAL(8,2)

Identify FLOAT that cannot fit into decimal for SQL Server 2008 R2:

SELECT Cost
FROM YourTable
WHERE LEN(CAST(Cost AS decimal(38, 2))) > 9 /*More than 8 digits   1 character for period*/