Performing Comparison on Floating Point Numbers in SQL Server

Q

How To Perform Comparison on Floating Point Numbers in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Comparison operations on approximate (floating point) numbers are also easy to understand. Just watch out rounding operations performed during conversions. Here are two examples of floating point number comparisons:

-- Rounding during implicit conversion makes them equal
DECLARE @x FLOAT(24), @y FLOAT(24);
SET @x = 0.999999999E+00;
SET @y = 0.99999999E+00;
SELECT CASE WHEN @x <> @y THEN 'True' ELSE 'False' END;
GO
False

-- Floating point number literals have double precision
-- So no rounding
SELECT CASE WHEN 0.999999999E+00 <> 0.99999999E+00 THEN
      'True'
   ELSE
      'False'
   END;
GO
True

 

Boolean Values and Logical Operations in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-01-21, 225👍, 0💬