Menu
I want to convert a varchar(max) column to decimal(10,4).
When I try to use cast or convert I am getting an arithmetic overflow exception. The issue is that the data stored in the varchar column may contain different precisions and different scales. For example, 123456789.1234567', 1.12345678 or 123456.1234.
For values like 123456.1234 it is converting with out any issue but for other values I am having some problems.
1000
3 Answers. Show activity on this post. The short answer is: No, it will hurt performance. The longer answer: VARCHAR fields are variable length, meaning, that the formatting of the database blocks cannot pre-account for the size of the data filling in there.
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
After testing I found that it was not the decimal place that was causing the problem, it was the precision (10)
This doesn't work: Arithmetic overflow error converting varchar to data type numeric.
DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594' SELECT CAST(@TestConvert AS DECIMAL(10, 4)) This worked
DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594' SELECT CAST(@TestConvert AS DECIMAL(13, 4)) Should be like 9 int + 4 floating = 13 chars
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
