?
Solved

Rounding Problem

Posted on 2005-04-28
15
Medium Priority
?
1,513 Views
Last Modified: 2008-01-09
Hello -

I am having a rounding problem in sql server.  Basically I am gathering output parameters in a stored procedure.  The parameter values are a float column in a table (the data is correct there).  When the output parameter is assigned a value the result is rouding to a different number.  The parameters are also declared as float.  I tried decimal and that did not work as well as the round function.  

For example (In table value = -66657.08) after
select @tf_netflow = netflow from @output where asset_desc = 'Total Fund'
value is -66657.1  

In most cases the decimal remainder is being completely dropped.

Anyone know what I am doing wrong here?
0
Comment
Question by:gigglick
  • 5
  • 5
  • 5
15 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13889676
Likely nothing.  Floating points values are only approximations -- you do not get total accuracy.  You need to store the original values as DECIMAL() if you need genuine accuracy and accurate rounding.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 13889680
That is, the original column tables need to be DECIMAL(), not FLOAT.  
0
 
LVL 5

Author Comment

by:gigglick
ID: 13889819
I didn't design the tables, and yes unfortunately they are float.  Is there now way around this?  Casting/Converting.  Someone has to have had a similar problem in the past.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 13889867
The best you can do is CAST it to a decimal.
0
 
LVL 5

Author Comment

by:gigglick
ID: 13889894
I get the following error when trying to use CAST
Server: Msg 8115, Level 16, State 6, Line 177
Arithmetic overflow error converting float to data type numeric.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13890239
Try increasing the digits in the decimal declaration; for example:

CAST(floatingColumn AS (19, 11))
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890254
I suspect Scott means:

CAST(floatingColumn AS Decimal(19, 11))
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13890324
OOPS, yep, you're right :-)
0
 
LVL 5

Author Comment

by:gigglick
ID: 13890328
Scott -

I increased it to 20 and still no cigar.  This time is dropped the entire remainder.  Looks like I'll have to find some other work around.  Maybe dump the items in a table variable and pass back the records. Oh what fun!

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890361
>>I increased it to 20 and still no cigar.<<
Why don't you post the values here, so that we can resolve this faster.  This is not rocket science.  Something like :

Select YourFloatColumnName
From YourTableName
Where SomeCondition
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13890366
Select the MIN() and MAX() values from that column, then tailor the DECIMAL to match that.  For example:

SELECT MIN(floatingColumn) AS [Min], MAX(floatingColumn) AS [Max]
FROM ...


If the MAX needs 22 whole number positions and the min needs 16 decimal positions, then do this:

CAST(floatingColumn AS DECIMAL(38, 16))
0
 
LVL 5

Author Comment

by:gigglick
ID: 13890371
select @tf_panc = CAST(pandc AS Decimal(19, 11))   from @output where asset_desc = 'Total Fund'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890378
You need to give us more than that.  What is the output?  How is the @tf_panc variable declared (hopefully decimal(19, 11) and not float or we will be back at square one)
0
 
LVL 5

Author Comment

by:gigglick
ID: 13890459
yes...the parameter was declared the same. (not float).  I increased to the max 38 and it worked.  

For example (In table value = -66657.08) after
select @tf_netflow = CAST(netflow AS Decimal(19, 11)) from @output where asset_desc = 'Total Fund'


value is  -66657.08   -Yeah...thanks again!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890626
I am glad you got it working.  I am not sure why you had to increase it to 38.  The following illustrates the problem and the solution:


Declare @X float

Set @X = -66657.08

Select @X, CAST(@X as decimal(18, 2))

-66657.080000000002      -66657.08

In any case, thanks for the points.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question