[Solved] SQLServerException: Arithmetic overflow error converting float to data type numeric


This SQLServerException is common with the applications using the MSSQL database. Once it occurs it generate the below stackTrace.

Exception Stack Trace

com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting float to data type numeric.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Reason of Exception

This issue occurred when your precision and scale argument is set two small for decimal column. For Example: NUMERIC(3,2) or DECIMAL(3,2) . It will allow maximum value as 9.99

Basically the first argument (precision) is the max number of digits (in this case 3) and the second argument (scale) is the number of digits to the right of the decimal point, which always takes away from the number of digits you can have to the left of the decimal point. So in this case, 3-2 = 1 digit allowed to the left of the decimal point, which is why allow max value can only be 9.99.

if you will try numeric value more than this size Ex : 10.9 then SQL server will throw exception as “SQLServerException: Arithmetic overflow error converting float to data type numeric” because size is more than the defined precision and scale of decimal point.

Solutions

You can follow any of these processes to resolve this issue:

  • Increase the precision and scale of decimal of your column based on your business requirement.
  • Convert the column type as float value so that accept values as per the set precision and scale.

Hope these processes resolved this issue. Please share your response in comments.

Happy Learning !!!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s