Arithmetic Overflow Mistake Converting Numeric To Information Type Numeric Inward Sql Server

This fault comes when you lot endeavor 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 forcefulness out agree is 999.99, if you lot endeavor to shop something similar 1000.00 too then it volition throw "Arithmetic overflow fault converting numeric to information type numeric". One of the mutual argue of this fault is the ignorance too misunderstanding of the NUMERIC information type. For example, many SQL Server DBAs too developers think that a NUMERIC(5,2) variable tin forcefulness out agree a seven digit floating betoken expose where five digit is earlier decimal too two digits are afterwards the decimal. This is wrong. Influenza A virus subtype H5N1 NUMERIC(5,2) means, the full expose of digits inwards the value cannot transcend five too decimal precision is two digits i.e. the maximum possible value is 999.99.

Another affair SQL programmers don't know too recollect virtually NUMERIC or DECIMAL information types inwards Microsoft SQL Server is that it doesn't throw this fault if you lot specify to a greater extent than digits than permitted afterwards the decimal point, instead it does rounding for instance if you lot shop 100.999 too then it volition shop 101.00 afterwards rounding.


Here is an SQL inquiry to essay 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 M merely the maximum value a NUMERIC(5,2) tin forcefulness out agree is 999 earlier the decimal point. You postulate to increase the width of the variable to shop this expose e.g. making @sample NUMERIC(6,2) volition solve this fault every bit shown below:

Here is to a greater extent than or less more SQL queries which volition confirm the demeanour of NUMERIC variable too 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 move on 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 afterwards rounding value volition endure out-of-range for defined numeric type SELECT @sample AS Result  Result Arithmetic overflow fault converting numeric to information type numeric.

That's all virtually "Arithmetic overflow fault converting numeric to information type numeric inwards SQL Server". You tin forcefulness out meet that motility of the fault is normally out-of-range value for the defined NUMERIC type. Just banking concern stand upwards for the source of value too right or increase the precision degree of your column.

Always recollect that NUMERIC(5,2) way full five digits amongst two digits afterwards the decimal point, too maximum value it tin forcefulness out agree is 999.99. Beware of rounding due to to a greater extent than additional digits afterwards decimal point, which tin forcefulness out too motility "Arithmetic overflow fault converting numeric to information type numeric" inwards Microsoft SQL Server.

Related SQL Server articles you lot may similar to explore

• Difference betwixt rank(), row_number(), too dense_rank() inwards SQL? (answer)
• How to replace NULL amongst empty String inwards SQL Server? (tutorial)
• Difference betwixt Cast, Convert, too Parse method inwards SQL? (answer)
• Difference betwixt coalesce() too isNull() inwards Microsoft SQL Server? (answer)
• How to withdraw duplicate rows from a tabular array inwards SQL? (solution)
• How to practise an Identity column inwards SQL Server? (example)
• How to format Date inwards Microsoft SQL Server? (example)
• 5 Web sites to larn SQL online for FREE? (resource)
• How to discovery the length of a String inwards SQL Server? (example)
• How to convert the final result of a SELECT ascendancy into a CSV String? (example)
• The right way to banking concern stand upwards for for NULL values inwards SQL query? (example)
• How to split upwards String inwards SQL Server 2008? (answer)
• What is the divergence betwixt unopen too deallocate a cursor? (answer)
• How to discovery all customers who accept never ordered? (solution)
• The right way to compare dates inwards SQL query? (example)
• How to add together columns into an existing tabular array inwards 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 you lot similar information given hither too my explanation too then delight part amongst your friends too colleagues. 

Sumber https://javarevisited.blogspot.com/

Share this post