Solved

# Rounding Problem

Posted on 2005-04-28
Medium Priority
1,513 Views
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
Question by:gigglick
• 5
• 5
• 5

LVL 70

Expert Comment

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

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

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

LVL 75

Assisted Solution

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

LVL 5

Author Comment

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

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

CAST(floatingColumn AS (19, 11))
0

LVL 75

Expert Comment

ID: 13890254
I suspect Scott means:

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

LVL 70

Expert Comment

ID: 13890324
OOPS, yep, you're right :-)
0

LVL 5

Author Comment

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

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

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

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

LVL 75

Expert Comment

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

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

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

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
Course of the Month7 days, 19 hours left to enroll