November 11, 2018 at 10:31 am
How can I convert the number to DECIMAL (4,1) and then back to varchar (this is a varchar (40) column that I am trying to update).
and avoid the Arithmetic Overflow error?
See the attached WORD DOC.
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.
Likes to play Chess
November 11, 2018 at 11:15 am
Like I said in your other topic please don't post things as attachments that can be included on your question's text. Include the details of what you are asking in your quesion. People are unlikely to download your document, which lowers the chance of you getting an answer. Things like sqlplans need to be provided as attachments, or very long SQL statements, if you are typing your question in a word doc, paste its contents into your quesion/topic instead
Why, however, are you converting a decimal(4,1) to a varchar(40)? The most characters a that that decimal could contain would be 5. What do you need the other 35 characters for?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 11, 2018 at 11:29 am
Thom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?
Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
Try this:SELECT MAX([Withdraw%]) maxWithdraw, MIN([Withdraw%]) minWithdraw
FROM MobilityRates2
And you might see where you problem is.
November 11, 2018 at 11:34 am
Jonathan AC Roberts - Sunday, November 11, 2018 11:29 AMThom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
You're right, musthave hit 9 one too many times times and not noticed. Thanks for the correction.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 11, 2018 at 6:15 pm
Jonathan AC Roberts - Sunday, November 11, 2018 11:29 AMThom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
Try this:SELECT MAX([Withdraw%]) maxWithdraw, MIN([Withdraw%]) minWithdraw
FROM MobilityRates2
And you might see where you problem is.
MAX returns 95.6 , and min = 0 (zero)
Likes to play Chess
November 11, 2018 at 6:20 pm
VoldemarG - Sunday, November 11, 2018 6:15 PMJonathan AC Roberts - Sunday, November 11, 2018 11:29 AMThom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
Try this:SELECT MAX([Withdraw%]) maxWithdraw, MIN([Withdraw%]) minWithdraw
FROM MobilityRates2
And you might see where you problem is.MAX returns 95.6 , and min = 0 (zero)
Does this give an error? SELECT CONVERT(decimal(4,1), [Withdraw%]) FROM MobilityRates2
November 12, 2018 at 5:38 am
Jonathan AC Roberts - Sunday, November 11, 2018 6:20 PMVoldemarG - Sunday, November 11, 2018 6:15 PMJonathan AC Roberts - Sunday, November 11, 2018 11:29 AMThom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
Try this:SELECT MAX([Withdraw%]) maxWithdraw, MIN([Withdraw%]) minWithdraw
FROM MobilityRates2
And you might see where you problem is.MAX returns 95.6 , and min = 0 (zero)
Does this give an error?
SELECT CONVERT(decimal(4,1), [Withdraw%]) FROM MobilityRates2
Also, check for values that won't convert using TRY_CONVERT:
SELECT * FROM MobilityRates2
WHERE TRY_CONVERT(decimal(4,1), [Withdraw%]) IS NULL
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 13, 2018 at 6:09 am
VoldemarG - Sunday, November 11, 2018 6:15 PMJonathan AC Roberts - Sunday, November 11, 2018 11:29 AMThom A - Sunday, November 11, 2018 11:21 AMAs for your error, the reason is because you have values greater than 9999.9. what are you expecting to happen to those values?Actually DECIMAL(4,1) will hold a maximum value of 999.9 and a minimum value of -999.9
Try this:SELECT MAX([Withdraw%]) maxWithdraw, MIN([Withdraw%]) minWithdraw
FROM MobilityRates2
And you might see where you problem is.MAX returns 95.6 , and min = 0 (zero)
Considering the OP implies they are storing the values as a varchar, then actually the above tells us nothing. '95.6' > '10000000000000000', so they could quite easier have a value between 1000, and 89999~. This is just one reason why poor data choices will always cause you more trouble that it's worth
Edit: in fact we can confirm this as in the word doc (which they are yet to put into their actual post... /Sigh) they have values greater than 100 in the image. 95.6 < 100 yet they stated the "max" value is less than 100. it clearly isn't I. Terms of numbers, but the OP is storing numbers as a string; and hence why their now having this problem.
Wrap the column name (not the Mon/max expression) in a convert to bigint, what do you get then? (Also, please put the content of the word doc in your post, it's got obvious detail that is missing and is causing this question tone far harder to answer for others).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply