Answered by:
Arithmetic overflow error converting numeric to data type numeric.
Question

Hi
I have a below scenario,DECLARE @zero_num numeric(5,4)
SET @zero_num = 0.00SELECT colA,colB,@Zero_num as ratio ,colC into #tmp
FROM table
UPDATE #tmp
SET ratio = colA/ colb WHERE colC 0Select * from #tmp
when we divide colA and ColB and assign to ratio columm iam getting overflow error because the output of the division was 248.9787565454656 .
So can you please suggest a work around.Iam using sql server 2008 R2.Thanks in advance
Answers

Hi Manish,
If you do not require the exact fractional output then you can use float data type. Else if you need the accurate fraction value then you can use the Decimal or Numeric data type with proper precision.
Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005
 Marked as answer by manish2kk Monday, January 6, 2014 2:38 PM

I want to display it in front end but when run the above query iam getting over flow error..I want to display as 248.9
DECLARE @IamSmall numeric(5,4) select @IamSmall = 9.9999  max value  no error by above select @IamSmall = 10.0  overflow /* Msg 8115, Level 16, State 8, Line 3 Arithmetic overflow error converting numeric to data type numeric. */
solution: Increase the total length of the numeric datatypeBalmukund Lakhani  Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker

This posting is provided "AS IS" with no warranties, and confers no rights.

My Blog  Team Blog  @Twitter
Author: SQL Server 2012 AlwaysOn  Paperback, Kindle Proposed as answer by Elvis LongModerator Monday, January 6, 2014 9:57 AM
 Marked as answer by Elvis LongModerator Tuesday, January 7, 2014 1:39 AM

i think you are confused about numeric data type precision and scale.
Precision
The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.
Scale
The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point.in your case your precision is 5 and scale is 4, that means you can not have more than single digit on the right side of the decimal point.
please refer below article
http://msdn.microsoft.com/enus/library/ms187746.aspx
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa  MCITP  Database Administrator 2008  My Blog  My Page Edited by Praveen Rayan D'sa Saturday, January 4, 2014 5:05 AM
 Proposed as answer by Elvis LongModerator Monday, January 6, 2014 9:56 AM
 Marked as answer by Elvis LongModerator Tuesday, January 7, 2014 1:39 AM
All replies



Hi Manish,
If you do not require the exact fractional output then you can use float data type. Else if you need the accurate fraction value then you can use the Decimal or Numeric data type with proper precision.
Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005
 Marked as answer by manish2kk Monday, January 6, 2014 2:38 PM

I want to display it in front end but when run the above query iam getting over flow error..I want to display as 248.9
DECLARE @IamSmall numeric(5,4) select @IamSmall = 9.9999  max value  no error by above select @IamSmall = 10.0  overflow /* Msg 8115, Level 16, State 8, Line 3 Arithmetic overflow error converting numeric to data type numeric. */
solution: Increase the total length of the numeric datatypeBalmukund Lakhani  Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker

This posting is provided "AS IS" with no warranties, and confers no rights.

My Blog  Team Blog  @Twitter
Author: SQL Server 2012 AlwaysOn  Paperback, Kindle Proposed as answer by Elvis LongModerator Monday, January 6, 2014 9:57 AM
 Marked as answer by Elvis LongModerator Tuesday, January 7, 2014 1:39 AM

i think you are confused about numeric data type precision and scale.
Precision
The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.
Scale
The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point.in your case your precision is 5 and scale is 4, that means you can not have more than single digit on the right side of the decimal point.
please refer below article
http://msdn.microsoft.com/enus/library/ms187746.aspx
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa  MCITP  Database Administrator 2008  My Blog  My Page Edited by Praveen Rayan D'sa Saturday, January 4, 2014 5:05 AM
 Proposed as answer by Elvis LongModerator Monday, January 6, 2014 9:56 AM
 Marked as answer by Elvis LongModerator Tuesday, January 7, 2014 1:39 AM