I am using SQL Server 2008 R2. Why is this comparison returning true? Values are 1.98 and 2.2, which is very different:
declare @num1 decimal = 1.98;
declare @num2 decimal = 2.2;
if(@num1 != @num2)
select 0;
else if(@num1 = @num2)
select 1;I am using SQL Server 2008 R2. Why is this comparison returning true? Values are 1.98 and 2.2, which is very different:
declare @num1 decimal = 1.98;
declare @num2 decimal = 2.2;
if(@num1 != @num2)
select 0;
else if(@num1 = @num2)
select 1;
2条答案
按热度按时间afdcj2ne1#
You have not included the precision and scale values in your declaration of
@num1,@num2variables. Hence, default values are being used.According to the documentation :
Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
So the scale of both variables defaults to 0 and thus both variables are being set to 2.
You can easily get around the problem by using declaration statements like:
w1jd8yoj2#
You have not specified the default precision and scale. The default precision is 18 and scale is 0 -- meaning there are no digits after the decimal point. See the documentation .
Hence, this is equivalent to:
SQL Server then assigns the values by converting the constants to the appropriate value, and both are being set to "2.".
You need to explicitly set the precision/scale if you want those values to be stored exactly.
The fact that the values are rounded is because both are decimals. This is not a general rule. It depends on the type, as described here .
When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The exact rules are described in the table below.