SQLServer Issues & Solutions

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

Leave a comment

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: decimal(3,2) or float(3,2) . It will float 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.

Note : This issue can also be occurred when you are trying to insert big values in form of scientific notation Ex: 1.5e100 because it’s precision value is high so throw exception.

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.
  • You can also use try_cast also to truncate and change the type of passing value.

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

Happy Learning !!!