Came across an odd thing when trying to convert a decimal that is a varchar into a negative.
Line 1
SELECT CONVERT(decimal(3,2),'0.01')*-1 as Line1 --correct way
Line1 -0.01
Shows the correct way, nothing weird there, as expected
Line 2
SELECT '0.01'*-1.0 as Line2 --original problem
Line2 0.00
I thought implicit conversion was going to be lucky enough here to just take care of the precedence and give me the -.01 that I was looking for. It instead returns 0.00.
Line 3
SELECT '0.1'*-1.0 as Line3 --moving decimal
Line3 -0.10
I wondered what moving the decimal place would do. That returns what I would have expected line 2 to return, just with .01 instead of .1.
Â
Line 4/5/6
SELECT '0.0499999999999999999'*-1.0 as Line4 --what is the limit?
SELECT '0.05'*-1.0 as Line5 --what is the limit (apparently .05)?
SELECT SQL_VARIANT_PROPERTY('0.05'*-1.0, 'basetype') as Line,SQL_VARIANT_PROPERTY('0.05'*-1.0, 'precision') as [6],SQL_VARIANT_Property('0.05'*-1.0, 'scale') as [ ] --what's the type
Line4 0.00
Line5 -0.10
Line 6 numeric 5 2
I then started to go down the path of "what is the final value needed before it changes". I incremented the ‘.01’ until I got something, which happened to be ‘.05’. At this point, it returns -.1. Using SQL_VARIANT_PROPERTY on that result returns a numeric(5,2) data type. I thought maybe it was a rounding thing, decided to go back just the tiniest bit. Still 0 (line 4) so the rounding thought seems to be correct.
Line 7
SELECT '0.09'*-1.0 as Line7 --what is the limit (apparently .05)?
Line7 -0.10
Repeat experiment of Line 4, just with a slightly larger number. Thought I might get something else.
Line 8
SELECT '0.01'*-1.00 as Line8 --works but with extra decimals in output
Line8 -0.0100
For some reason, adding another 0 to the end of the -1.0 ends up with a numeric(7,4) instead, while also converting the varchar to the negative (albeit with extra unwanted scale). Further adding a 0 to the -1.0 increases the precision and scale by 2 each i.e. -1.000 gives numeric(9,6), -1.0000 gives numeric(11,8), etc.
What is the behavior behind this? Why would adding 2 zeroes get me the expected value when 1 does not, even though both are decimals?
>Solution :
TL;DR: Don’t use varchars to represent numerical values, and if you are be explicit with the target data type. Better yet, fix the problem at the root.
All these results as expected. For Line 2 you implicitly cast '0.01' to a decimal(2,1), which is 0.0, so you get 0.00 after the multiplication in the resulting decimal(5,2):
SELECT '0.01'*-1.0 as Line2,
CAST('0.01' AS decimal(2,1))*-1.0,
0.0*-1.0
For line 3 the logic is identical, apart from you have '0.1', so precision isn’t lost.
Again, the same for Lines 4-7; you’re casting to a decimal(2,1), and that value is rounded to, 0.04999~ is closer to 0 than 0.1, while 0.05 is closer to 0.1; this is basic rounding.
Line 8, you implicitly cast to a decimal(3,2) and so you get 0.01 and thus a different result.
All of this would be avoided if you were explicit with your casting/converting and specified the precision you need:
SELECT CONVERT(decimal(3,2),'0.01')*-1.0;
But really you shouldn’t be using a string based data type for numerical data in the first place; use the correct data type to start with. Here you should just be typing 0.01 rather than '0.01'.