CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Snehal
Snehal

Reputation: 1088

Why Query Output Different even if both the queries are same?

I am using SQL 2008 and running following Query.

Select ROUND(53.67*0.75,3)

Which gives me result = 40.2530

But same thing if i am checking with data stored in table it give me different output why?

Create table CalCheck
(GrossWeight float, Rate float) 
Go
Insert into CalCheck values (53.67,0.75)
GO
Select ROUND(GrossWeight*Rate,3) from CalCheck

Above query gives me result = 40.252

Why its showing 0.001 difference?

Upvotes: 1

Views: 81

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

This is because SQL SERVER is interpreting the values as DECIMALS in the first example 

Select ROUND(53.67*0.75,3)

Whereas the second example you are specifying FLOATS

Have a look at this example

SQL Fiddle DEMO

Further to that you could have a look at SQL_VARIANT_PROPERTY

Returns the base data type and other information about a sql_variant value.

Select  sql_variant_property(53.67*0.75,'BaseType') BaseType, 
        sql_variant_property(53.67*0.75,'Precision') [Precision], 
        sql_variant_property(53.67*0.75,'scale') [Scale]

From Using decimal, float, and real Data

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

Upvotes: 4

Related Questions