April 10, 2014 at 4:32 am
Hi
I have this code below.
create table dbo.Test(Base_fee [numeric](9, 6) NULL)
insert into dbo.Test(Base_fee) values (444444444.666666)
when I run it I get Arithmetic overflow error. my data type is (9, 6) So I don't expect it to throw this error, what is the cause?
April 10, 2014 at 4:46 am
hoseam (4/10/2014)
HiI have this code below.
create table dbo.Test(Base_fee [numeric](9, 6) NULL)
insert into dbo.Test(Base_fee) values (444444444.666666)
when I run it I get Arithmetic overflow error. my data type is (9, 6) So I don't expect it to throw this error, what is the cause?
Because the you've given a precision of 9, which means that total number of units that you can store is 9 (including the numbers after the decimal place). If you want to store the value you've written above, try NUMERIC(15, 6).
April 10, 2014 at 4:53 am
I tried to reduce the total number to 111111.11 I'm still getting the same error.
April 10, 2014 at 4:54 am
With the Numeric data type, the first digit in the bracket gives the maximum number of digits for the complete figure and the second is the maximum number of digits after the decimal point. This number is subtracted from the first number to give the maximum number of digits allowed to the left of the decimal point.
For example, if your data type is numeric(9,6), the total number of digits allowed is 9, with a maximum of 6 of them on the right of the decimal point i.e. 123.456789. You could also have 1234.56789 because there are 5 digits following the decimal point, allowing 4 before it. You can't have 12.3456789 though because that has 7 digits following the decimal point although it still consists of only 9 digits.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 10, 2014 at 5:01 am
I tried 123.456789 and it worked fine,
Then I tried 1234.56789 I got the same error:
Msg 8115, Level 16, State 8, Line 32
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
April 10, 2014 at 5:19 am
BWFC (4/10/2014)
With the Numeric data type, the first digit in the bracket gives the maximum number of digits for the complete figure and the second is the maximum number of digits after the decimal point. This number is subtracted from the first number to give the maximum number of digits allowed to the left of the decimal point.For example, if your data type is numeric(9,6), the total number of digits allowed is 9, with a maximum of 6 of them on the right of the decimal point i.e. 123.456789. You could also have 1234.56789 because there are 5 digits following the decimal point, allowing 4 before it. You can't have 12.3456789 though because that has 7 digits following the decimal point although it still consists of only 9 digits.
I'm sorry Hoseam, I led you astray here with a bad example. :blush:
When the second number, in this case 6, is subtracted from the first number, here 9, the difference (3), is the maximum number of digits allowed on the left of the decimal point. This means that 12.345678 is allowed, because it has fewer than 3 digits on the left of the decimal point and 6 on the right, but 1234.56789 is not, because it has more than 3 digits on the left of the point even though it has fewer than 6 on the right.
I hope that is a bit clearer.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply