Prev: SQL logs
Next: query problem
From: Baiju K U on 29 Jun 2010 03:18
Hello

I have a column called 'Area' in my table which is rounded to 2 decimal
places. The data to be stored in this column is rounded to 2 decimal places
and then only inserting to the table. If I take a sum of this column the
result is not rounding to 2 decimal places. Why is that? How is sum
implemented on Float data types in SQL Server?

Thanks
Baiju


From: Erland Sommarskog on 29 Jun 2010 05:29
Baiju K U (baiju(a)indus-systems.com) writes:
> I have a column called 'Area' in my table which is rounded to 2 decimal
> places. The data to be stored in this column is rounded to 2 decimal
> places and then only inserting to the table. If I take a sum of this
> column the result is not rounding to 2 decimal places. Why is that? How
> is sum implemented on Float data types in SQL Server?

There is no such thing as 2 decimal places with float values. A float
value is a 53-bit number with an exponent. Most decimal numbers cannot
be represented exactly in a float value. This is nothing that is peculiar
to SQL Server but applies computing in general and you can run into the
issue in any language that permits to use float.

If you want exact decimal values, use the decimal data type.

Also observe that what you see of a float value is an artefact of the client
tool. If you run this in SQL Server Management studio:

select 1.2345E0

You will see:

1.2345

But if you run the same thing from OSQL, you will see:

1.2344999999999999

This is because OSQL uses a different routine to convert float values
to strings than SSMS.

Note that none of these values are the truth but only approximation of
the binary number.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 29 Jun 2010 13:25
You should have had a class on the IEEE-754 Standard and floatign
point math in general. Start here:

http://en.wikipedia.org/wiki/IEEE_754-2008
From: Baiju K U on 30 Jun 2010 00:51
Thanks for the info. I founded some more info on this from the web.


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DA674D4D77DBYazorman(a)127.0.0.1...
> Baiju K U (baiju(a)indus-systems.com) writes:
>> I have a column called 'Area' in my table which is rounded to 2 decimal
>> places. The data to be stored in this column is rounded to 2 decimal
>> places and then only inserting to the table. If I take a sum of this
>> column the result is not rounding to 2 decimal places. Why is that? How
>> is sum implemented on Float data types in SQL Server?
>
> There is no such thing as 2 decimal places with float values. A float
> value is a 53-bit number with an exponent. Most decimal numbers cannot
> be represented exactly in a float value. This is nothing that is peculiar
> to SQL Server but applies computing in general and you can run into the
> issue in any language that permits to use float.
>
> If you want exact decimal values, use the decimal data type.
>
> Also observe that what you see of a float value is an artefact of the
> client
> tool. If you run this in SQL Server Management studio:
>
> select 1.2345E0
>
> You will see:
>
> 1.2345
>
> But if you run the same thing from OSQL, you will see:
>
> 1.2344999999999999
>
> This is because OSQL uses a different routine to convert float values
> to strings than SSMS.
>
> Note that none of these values are the truth but only approximation of
> the binary number.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


 | 
Pages: 1
Prev: SQL logs
Next: query problem