Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 3rd, 2004, 11:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default Rounding

I have a database field that is set to a float data type. I have entered 59.47 in the database. When i view it in Enterprise manager it says 59.47 which it should. If I view it in Query analyzer (or in any functions of procedures) it comes up as 59.469999999999999. If I do a round on it for 1 decimal place, I get 59.5, but if I do 2, I get 59.469999999999999. What I want is 59.47. I am not sure why I am getting what I am getting when that is not what is in the database. Can anyone help me here? I just need it to be 59.47 so my calculations come out right. If there is another datatype that is better, then I will use it, but I have not had any luck so far and am very frustrated over this issue.

Chris
__________________
Chris
  #2 (permalink)  
Old June 3rd, 2004, 11:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

You could use a decimal, with a precision of 5 and a scale of 2, so that it can only have two decimal points. That really depends on the number of decimals to the right needed. I think the issue might be how Query Analyzer returns the results, more than it being an actual problem though...

Brian
  #3 (permalink)  
Old June 6th, 2004, 06:01 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Romania.
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'm pretty sure that the value stored in the database is 59.469999999999999, and EM (enterprise manager) round it to 2 decimal places. this is how floating point numbers are like: not all values can be represented exactly, but with a better precision than the fixed point numbers (in this case the precision is 0.000000000000001). if you would use the money data type, you will store in this case the exact 59.47 value, since the money data type is a decimal data type with 4 decimals.

or you could round the float value when you display it with something like this:

declare @flt float

set @flt = 59.47

select cast(@flt as decimal(10, 2))
  #4 (permalink)  
Old June 7th, 2004, 10:46 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Kilika,

Storing 59.47 in a real or a float in SQL server is like dividing 1 by 3. Despite the number of decimals you specify (float or real) the stored value isn't the exact value.

QA reports the exact value when used with it's default configuration setting. If you configure it to use regional settings you will find it displays the correct value (for float as for real).

You can find this setting at Tools/options connection tab.

Float and real are IEEE defined datatypes and so are there recommendations for rounding. As QA uses ODBC to access the data it's the ODBC layer that does the rounding. Can't remember the exact function call's. But get yourself a copy of the ODBC documentation and you should be able to use it in your own app.

Regards,

Jago
  #5 (permalink)  
Old June 1st, 2005, 03:36 PM
Registered User
 
Join Date: Jun 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jago is right. It look funky, but MS official statement is that they compile with the ieee standard. Do what Jago sez, change the setting on your QA. My question is...How does a database field defined as a float comply with Sarbanes/Oxley ???


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding problem rajanikrishna Classic ASP Basics 1 August 17th, 2006 12:14 AM
Rounding JasperGIS Beginning VB 6 1 December 14th, 2005 03:09 PM
What's with the rounding?? kaizer BOOK: Beginning Java 2 2 December 22nd, 2003 11:36 PM
Rounding in C# cjo ASP.NET 1.0 and 1.1 Basics 3 November 3rd, 2003 04:12 PM
Rounding Droopy Classic ASP Basics 3 August 14th, 2003 08:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.