question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked MelissaMa-msft commented

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

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 ?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PadmanabhanVenkatesh-6789,

Could you please validate the lastest answers and provide any update?

If they are still not working, please provide your sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered PadmanabhanVenkatesh-6789 commented

Hi @PadmanabhanVenkatesh-6789,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI.
Thanks for the reply. I tried with 38, which did not work,
should the precision scale of 4 needs to be changed ?

I can try the method "try_cast" , but the output cannot be set as Null. How to fix this ?
Thanks

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @PadmanabhanVenkatesh-6789,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.