Arithmetic overflow error converting numeric to data type numeric, but value is shorter than specified...
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.
sql
sql-server tsql edited Jan 3 at 14:39
Tab Alleman
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
add a comment |
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.
sql
sql-server tsql edited Jan 3 at 14:39
Tab Alleman
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
3
Why do you show us the column definition oftd_long_shrbut your query is updatingtd_long_shr_qty? Are you looking at the wrong column definition?
– Tab Alleman
Jan 3 at 13:47
Sorry i have updated the column name
– xrx215
Jan 3 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.
– jarlh
Jan 3 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.
– Damien_The_Unbeliever
Jan 3 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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52
add a comment |
0
0
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.
sql
sql-server tsql edited Jan 3 at 14:39
Tab Alleman
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
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.
sql
sql-server tsql sql
sql-server tsql edited Jan 3 at 14:39
Tab Alleman
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
edited Jan 3 at 14:39
Tab Alleman
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
edited Jan 3 at 14:39
Tab Alleman
27.5k62442
edited Jan 3 at 14:39
Tab Alleman
27.5k62442
edited Jan 3 at 14:39
Tab Alleman
27.5k62442
27.5k62442
asked Jan 3 at 13:44
xrx215xrx215
41062136
asked Jan 3 at 13:44
xrx215xrx215
41062136
asked Jan 3 at 13:44
xrx215xrx215
41062136
41062136
3
Why do you show us the column definition oftd_long_shrbut your query is updatingtd_long_shr_qty? Are you looking at the wrong column definition?
– Tab Alleman
Jan 3 at 13:47
Sorry i have updated the column name
– xrx215
Jan 3 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.
– jarlh
Jan 3 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.
– Damien_The_Unbeliever
Jan 3 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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52
add a comment |
3
Why do you show us the column definition oftd_long_shrbut your query is updatingtd_long_shr_qty? Are you looking at the wrong column definition?
– Tab Alleman
Jan 3 at 13:47
Sorry i have updated the column name
– xrx215
Jan 3 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.
– jarlh
Jan 3 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.
– Damien_The_Unbeliever
Jan 3 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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52
3
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?– Tab Alleman
Jan 3 at 13:47
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?– Tab Alleman
Jan 3 at 13:47
Sorry i have updated the column name
– xrx215
Jan 3 at 13:48
Sorry i have updated the column name
– xrx215
Jan 3 at 13:48
3
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.
– jarlh
Jan 3 at 13:48
1720187931245 is more than 12 digits. numeric(18,6) means 18 digits in total, where are 12 before the decimal point, and 6 after.
– jarlh
Jan 3 at 13:48
2
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.– Damien_The_Unbeliever
Jan 3 at 13:49
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.– Damien_The_Unbeliever
Jan 3 at 13:49
1
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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52
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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52
add a comment |
1 Answer
1
active
oldest
votes
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).
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
1
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit intoNUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
– Tim Biegeleisen
Jan 3 at 13:55
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023516%2farithmetic-overflow-error-converting-numeric-to-data-type-numeric-but-value-is%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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).
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
1
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit intoNUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
– Tim Biegeleisen
Jan 3 at 13:55
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
|
show 3 more comments
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).
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
1
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit intoNUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
– Tim Biegeleisen
Jan 3 at 13:55
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
|
show 3 more comments
10
10
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).
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
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).
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
edited Jan 3 at 14:06
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
answered Jan 3 at 13:49
Tim BiegeleisenTim Biegeleisen
235k13100160
235k13100160
1
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit intoNUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
– Tim Biegeleisen
Jan 3 at 13:55
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
|
show 3 more comments
1
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
@ZoharPeled But then wouldn't that mean that the OP's number would in fact fit intoNUMBER(18, 6), since only 4 significant figures on the RHS means that there would be 2 extra places on the LHS?
– Tim Biegeleisen
Jan 3 at 13:55
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
1
1
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.
– Zohar Peled
Jan 3 at 13:51
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.
– Zohar Peled
Jan 3 at 13:51
thank you i got it...
– xrx215
Jan 3 at 13:53
thank you i got it...
– xrx215
Jan 3 at 13:53
@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?– Tim Biegeleisen
Jan 3 at 13:55
@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?– Tim Biegeleisen
Jan 3 at 13:55
1
1
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
@ZoharPeled Where did you find that in the documentation??? because I don't see it here: docs.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.
– Tab Alleman
Jan 3 at 13:59
2
2
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
@EricBrandt thanks! MSDN should put that sentence in flaming bold letters in a bright yellow box.
– Tab Alleman
Jan 3 at 14:33
|
show 3 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023516%2farithmetic-overflow-error-converting-numeric-to-data-type-numeric-but-value-is%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Why do you show us the column definition of
td_long_shrbut your query is updatingtd_long_shr_qty? Are you looking at the wrong column definition?– Tab Alleman
Jan 3 at 13:47
Sorry i have updated the column name
– xrx215
Jan 3 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.
– jarlh
Jan 3 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.– Damien_The_Unbeliever
Jan 3 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. docs.microsoft.com/en-us/sql/t-sql/data-types/…
– scsimon
Jan 3 at 13:52