How to avoid error : Arithmetic overflow error converting float to data type numeric.

Question

Hi.

I have 2 SQL queries which are running fine in few databases but in some I am getting the error as : Arithmetic overflow error converting float to data type numeric

Below are the queries used:

SELECT

Format(CAST(COLUMNAME1 AS DECIMAL(30,4)),'+00000000000000000000000000.0000;-00000000000000000000000000.0000'),

Format(CAST(COLUMNNAME2 AS DECIMAL(30,4)),'+00000000000000000000000000.0000;-00000000000000000000000000.0000')

FROM TableName

SELECT 9, COUNT(*)+2 ,

Format(SUM(CAST(ISNULL(NULLIF(COLUMNNAME1, ''),0) AS DECIMAL(30,4))) ,'+000000000000000000000000000.0000;-000000000000000000000000000.0000') AS CHEC0,

Format(SUM(CAST(ISNULL(NULLIF(COLUMNNAME2, ''),0) AS DECIMAL(30,4))),'+000000000000000000000000000.0000;-000000000000000000000000000.0000') AS CHEC1

FROM TableName

what should be edited in the above 2 queries to avoid this error ? I believe this is a Data issue, but is there any code fix which can be set ?

Answer 1

Hi @Padmanabhan, Venkatesh ,

Thank you so much for posting here in Microsoft Q&A.

You will face 'Arithmetic overflow error converting float to data type numeric' error if you would like to convert one number whose precision of size is bigger than 30 to DECIMAL(30,4) like below example:

select Format(CAST(123456789123456789123456789123456.2541 AS DECIMAL(30,4)),'+00000000000000000000000000.0000;-00000000000000000000000000.0000')  

In this case, you could firstly try to replace DECIMAL(30,4) with DECIMAL(38,4) as below since the maximum allowed size given to the type 'decimal' is (38).

select Format(CAST(1234567891234567891234564285378.2541 AS DECIMAL(38,4)),'+00000000000000000000000000.0000;-00000000000000000000000000.0000')  

Output:

+1234567891234567891234564285378.2541  

If above is not working, then you could have a try to replace cast with try_cast as below since try_cast returns null if a value is failed to cast to the specified data type.

select Format(TRY_CAST(1234567891234567891234564285378.2541 AS DECIMAL(30,4)),'+00000000000000000000000000.0000;-00000000000000000000000000.0000')  

Output:

NULL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

For example, if COLUMNAME1 is 1.5e100, then it cannot be converted to DECIMAL(30,4).

If you want to ignore such large numbers, showing NULL, then use TRY_CAST instead of CAST.

Answer 3

If the output cannot be set to NULL, you first need to find out whether the data is correct. That is, are values > 1E34 legit? If they are not, you will need to filter out this data. Or better, have the data cleaned up.

If the data is legit, you will need to go back to the drawing board and figure out what you really want to achieve. Since the decimal data type in SQL Server only supports 38 digits, it is a lost cause. Which it is anyway, because there is not really a point formatting a floating-point number of, say, 1E30 with 30 digits, since the precision is only 15-16 digits anyway.

Answer 4

Hi @Padmanabhan, Venkatesh ,

Thanks for your update.

It is recommended to post DDL of your table together with INSERT statements with sample data. Then we could look into it and find out any possible solution.

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.

If both DECIMAL(38,4) and try_cast are not working for you, as mentioned by Erland, you have to filter out the data who has the value from - 10^38 +1 through 10^38 - 1.

Or you could try with cast( column as float) instead of cast(column as DECIMAL(30,4)) like below:

select Format(CAST(123456789123456789871234564285378.2541 AS FLOAT),'+00000000000000000000000000.0000;-00000000000000000000000000.0000')  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I was facing a similar problem. However, I was casting my data as INT, when the error popped up. I tried to do many things as per several suggestions, but noting worked at all.

Finally I tried to cast as BIGINT and BINGO!!!!!!! It worked.....

Hope this helps someone who is facing similar problem