SQL doesnt want to round??

Question

Post reply

SQL doesnt want to round??

brett.davis1

SSChasing Mays

Points: 608
More actions
November 4, 2008 at 3:33 am

#200730

Hi all,
I'm trying to do some rounding of numbers - these are financial figures so it needs to round properly so CIELING() or FLOOR() probably wont do it.
The problem is that Im not getting the results I expected.
Sample data:
DECLARE @a FLOAT
DECLARE @b-2 FLOAT
DECLARE @C FLOAT
SET @a = 538.21000000000004
SET @b-2 = 31.430000000000003
SET @C = 17.380000000000003
SELECT ROUND(@a,2),ROUND(@b,2),ROUND(@c,2)
Now I need to get 538.21, 31.43 and 17.38 for variables a, b and c respectively, however that's not whats happening for a and c, however b seems to work fine.
If I run the above statememt I get:
538.2100000000000431.4317.379999999999999
What gives?
Thanks,
Brett

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Got to admit, that looks strange. I've just pasted your code into a query window and it returns the expected results.

Only thing I can think of is to explicitly supply a value of 0 to the function parameter of the ROUND function.

SELECT ROUND(@a,2,0),ROUND(@b,2,0),ROUND(@c,2,0)

Karl
source control for SQL Server

Peter Lavelle Mr or Mrs. 500 Points: 526 More actions · Answer 2

Hi Brett,

Have you considered the MONEY data type for financial data rather than FLOAT which is imprecise?

Would you really have financial data to 14 decimal places?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

I get the correct answers by copying and pasting the code directly from the original post in SQL Server 2005...

In SQL Server 2000, no dice... @a and @C both come back as FLOATS in both the grid mode and the text mode. I'm trying to figure out why, but other than declaring it as a bug, I probably won't be able to figure out why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Heh... I believe I have it... ROUND just shouldn't be used for display purposes... in Books Online, they say...

This example shows two expressions illustrating that with the ROUND function the last digit is always an estimate.

And a simple modification of the code seems to support that...

DECLARE @a FLOAT

--SET @a = 538.21400000000004

SET @a = 538.21500000000004

SELECT

ROUND(@a,0),

ROUND(@a,1),

ROUND(@a,2),

ROUND(@a,3)

SELECT

STR(@a,6,0),

STR(@a,6,1),

STR(@a,6,2),

STR(@a,6,3)

SELECT

CAST(@a AS DECIMAL(9,0)),

CAST(@a AS DECIMAL(9,1)),

CAST(@a AS DECIMAL(9,2)),

CAST(@a AS DECIMAL(9,3))

Notice that ROUND does do the rounding at the correct digit... it just that ROUND is a "Floating Point" function... I'd continue to use FLOAT for the complex calculations you want, but, like any good calculator, I'd do a little display formatting with either STR or CAST to display the final answer in a form that humans understand instead of what a computer understands... FLOATs are actually decimal representations of binary numbers. Not all decimal numbers have binary equivalents and that's where the 538.2100000004 stuff sometimes comes into play.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I think the crux of the issue is the comment (from BOL in the remarks section on ROUND).

ROUND
Returns a numeric expression, rounded to the specified length or precision.
Syntax
ROUND ( numeric_expression , length [ , function ] )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
length
Is the precision to which numeric_expression is to be rounded. length must be tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal places specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.
function
Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.
Return Types
Returns the same type as numeric_expression.

Meaning - it doesn't change the data type to a precise data type, but rather - preserves it in the same data type it was in.......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Heh... no fair... you gave a nice short explanation... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)