0

I have a column which is defined in the table as follows

td_long_shr_qty (numeric(18,6), null)

when i update the column I am getting an error

UPDATE
    fact
SET
    td_long_shr_qty = 1720187931245.8069
WHERE
    id = 29

arithmetic overflow error converting numeric to data type numeric.

is there any issue with this number 1720187931245.8069 as it is less than precison 18 why do i get this error.please help.

Improve this question
5
  • 3
    Why do you show us the column definition of td_long_shr but your query is updating td_long_shr_qty? Are you looking at the wrong column definition? Commented Jan 3, 2019 at 13:47
  • Sorry i have updated the column name Commented Jan 3, 2019 at 13:48
  • 3
    1720187931245 is more than 12 digits. numeric(18,6) means 18 digits in total, where are 12 before the decimal point, and 6 after. Commented Jan 3, 2019 at 13:48
  • 2
    numeric(18,6) is "18 digits, with 6 of those appearing after the decimal point". You cannot fit the number you're showing us into such a type. Commented Jan 3, 2019 at 13:49
  • 1
    No it's not. Your defined precision is 18 with a scale of 6. The value you are trying to enter has a precision of 17 with a scale of 4. learn.microsoft.com/en-us/sql/t-sql/data-types/… Commented Jan 3, 2019 at 13:52

1 Answer 1

Reset to default
10

The meaning of NUMERIC(18, 6) is 18 total places of precision, 6 of which are to the right of the decimal point (if present). So, this means that the largest number which this type can hold is:

 999999999999.999999
1720187931245.8069

I have deliberately pasted your number from the query below, and lined up with, the largest possible value. It should be plain for you to see that it exceeds the capacity of NUMERIC(18, 6).

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

You might want to include the relevant quot from ducomentation: "s (scale) The number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point." other then that, +1.
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit into NUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
@TimBiegeleisen not the way I understand it. your explanation is correct - the maximum number of the digits left to the decimal separator is the total number of digits (precision) minus the maximum number of digits to the right of the decimal separator (scale). So if the data type is `numeric(18,6) it can store up to 12 digits left of the decimal separator, just like you've explained.
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: learn.microsoft.com/en-us/sql/t-sql/data-types/… The sentence in your comment is a lot clearer than what's in the documentation I found.
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.