February 22, 2009 at 6:57 pm
I wonder if someone can help me with a puzzling result when comparing two sql_variant variables.
@VarFloat is a sql_variant that contains a float with the value 1.23.
@VarNumeric is a sql_variant that contains a numeric(3,2) with the value 1.23.
What is interesting is that when compared they appear as not being equal. However this does not happen in a similar comparison between two sql_variant that contain a decimal(3,2) and a numeric(3,2) respectively.
Here is the script that gives the surprising result:
DECLARE @VarFloat sql_variant
DECLARE @VarNumeric sql_variant
SET @VarFloat = CONVERT(float, 1.23)
SET @VarNumeric = CONVERT(numeric(3,2), 1.23)
PRINT CAST(@VarFloat as varchar)
PRINT CAST(@VarNumeric as varchar)
IF (@VarFloat = @VarNumeric) PRINT 'Equal. Case: Sql_Variant float vs. numeric.'
ELSE Print 'Not Equal. Case: Sql_Variant float vs. numeric.'
Here is a complete script that contains more such comparisons in case they shed more light on my question:
DECLARE @VarFloat sql_variant
DECLARE @VarNumeric sql_variant
SET @VarFloat = CONVERT(float, 1.23)
SET @VarNumeric = CONVERT(numeric(3,2), 1.23)
PRINT CAST(@VarFloat as varchar)
PRINT CAST(@VarNumeric as varchar)
IF (@VarFloat = @VarNumeric) PRINT 'Equal. Case: Sql_Variant float vs. numeric.'
ELSE Print 'Not Equal. Case: Sql_Variant float vs. numeric.'
GO
-- ===========================================
DECLARE @VarDecimal sql_variant
DECLARE @VarNumeric sql_variant
SET @VarDecimal = CONVERT(decimal(3,2), 1.23)
SET @VarNumeric = CONVERT(numeric(3,2), 1.23)
IF (@VarDecimal = @VarNumeric) PRINT 'Equal. Case: Sql_Variant decimal vs. numeric.'
ELSE Print 'Not Equal. Case: Sql_Variant decimal vs. numeric.'
GO
-- ===========================================
DECLARE @float float
DECLARE @Numeric numeric(3,2)
SET @float = 1.23
SET @Numeric = 1.23
IF (@Float = @Numeric) PRINT 'Equal. Case: decimal vs. numeric.'
ELSE Print 'Not Equal. Case: decimal vs. numeric.'
February 22, 2009 at 7:16 pm
[font="Verdana"]Floats have issues with representing certain numbers. There are some numbers (for example, 0.3) that they cannot represent other than inexactly. Also, conversions between different numeric types and floating point types are imprecise.
If you are doing comparisons against floats, you should do them as range comparisons. So if you are comparing two float values, A and B, you should use an inexact comparison such as:
A between B - 0.01 and B + 0.01
or
abs(A-B) < 0.01
Unless you are storing engineering or mathematical data, I recommend you avoid floating point values within a database.
[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply