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)indussystems.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 53bit 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/enus/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/enus/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 IEEE754 Standard and floatign point math in general. Start here: http://en.wikipedia.org/wiki/IEEE_7542008
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)indussystems.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 53bit 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/enus/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/enus/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >

Pages: 1 Prev: SQL logs Next: query problem 