Arithmetic Overflow Fault Converting Numeric To Information Type Numeric Inwards Sql Server

This fault comes when y'all travail to shop an out-of-range floating betoken value into a numeric variable. For example, if your NUMERIC or DECIMAL variable is defined every bit NUMERIC(5,2) than the maximum value it tin concord is 999.99, if y'all travail to shop something similar 1000.00 as well as then it volition throw "Arithmetic overflow fault converting numeric to information type numeric". One of the mutual argue of this fault is the ignorance as well as misunderstanding of the NUMERIC information type. For example, many SQL Server DBAs as well as developers think that a NUMERIC(5,2) variable tin concord a vii digit floating betoken reveal where five digit is earlier decimal as well as two digits are later on the decimal. This is wrong. Influenza A virus subtype H5N1 NUMERIC(5,2) means, the full reveal of digits inward the value cannot plow over five as well as decimal precision is two digits i.e. the maximum possible value is 999.99.

Another affair SQL programmers don't know as well as retrieve most NUMERIC or DECIMAL information types inward Microsoft SQL Server is that it doesn't throw this fault if y'all specify to a greater extent than digits than permitted later on the decimal point, instead it does rounding for instance if y'all shop 100.999 as well as then it volition shop 101.00 later on rounding.


Here is an SQL inquiry to seek out this points:

DECLARE @sample NUMERIC(5,2) SET @sample = 1000.554 SELECT @sample

Output
Arithmetic overflow fault converting numeric to information type numeric.


Explanation: 
This fourth dimension SQL Server throws the fault because nosotros are trying to shop chiliad only the maximum value a NUMERIC(5,2) tin concord is 999 earlier the decimal point. You involve to increase the width of the variable to shop this reveal e.g. making @sample NUMERIC(6,2) volition solve this fault every bit shown below:

Here is about more SQL queries which volition confirm the behaviour of NUMERIC variable as well as its range:

DECLARE @sample NUMERIC(5,2) SET @sample = 100.554 // no rounding because the extra digit is less than 5 SELECT @sample AS Result  Result 100.55  DECLARE @sample NUMERIC(5,2) SET @sample = 100.555 // rounding volition tumble out SELECT @sample AS Result  Result 100.56   DECLARE @sample NUMERIC(5,2) SET @sample = 100.55 // no rounding because value is nether defined precision SELECT @sample AS Result  Result 100.55   DECLARE @sample NUMERIC(5,2) SET @sample = 100.999 // Rounding to nearest value SELECT @sample AS Result  Result 101.00  DECLARE @sample NUMERIC(5,2) SET @sample = 999.999 // fault because later on rounding value volition hold upwards out-of-range for defined numeric type SELECT @sample AS Result  Result Arithmetic overflow fault converting numeric to information type numeric.

That's all most "Arithmetic overflow fault converting numeric to information type numeric inward SQL Server". You tin run across that displace of the fault is unremarkably out-of-range value for the defined NUMERIC type. Just banking company represent the source of value as well as right or increase the precision degree of your column.

Always retrieve that NUMERIC(5,2) agency full five digits amongst two digits later on the decimal point, as well as maximum value it tin concord is 999.99. Beware of rounding due to to a greater extent than additional digits later on decimal point, which tin every bit good displace "Arithmetic overflow fault converting numeric to information type numeric" inward Microsoft SQL Server.

Related SQL Server articles y'all may similar to explore

• Difference betwixt rank(), row_number(), as well as dense_rank() inward SQL? (answer)
• How to replace NULL amongst empty String inward SQL Server? (tutorial)
• Difference betwixt Cast, Convert, as well as Parse method inward SQL? (answer)
• Difference betwixt coalesce() as well as isNull() inward Microsoft SQL Server? (answer)
• How to take away duplicate rows from a tabular array inward SQL? (solution)
• How to exercise an Identity column inward SQL Server? (example)
• How to format Date inward Microsoft SQL Server? (example)
• 5 Web sites to larn SQL online for FREE? (resource)
• How to uncovering the length of a String inward SQL Server? (example)
• How to convert the resultant of a SELECT ascendancy into a CSV String? (example)
• The right way to banking company represent for NULL values inward SQL query? (example)
• How to dissever String inward SQL Server 2008? (answer)
• What is the departure betwixt closed as well as deallocate a cursor? (answer)
• How to uncovering all customers who bring never ordered? (solution)
• The right way to compare dates inward SQL query? (example)
• How to add together columns into an existing tabular array inward MSSQL? (example)

Further Reading

Querying Microsoft SQL Server Training Kit for Exam 70-461

Microsoft SQL Server T-SQL Fundamentals

Thanks for reading this article. If y'all similar information given hither as well as my explanation as well as then delight percentage amongst your friends as well as colleagues.