Post reply

SQL doesnt want to round??

  • 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.21000000000004 31.43 17.379999999999999

    What gives?

    Thanks,

    Brett

  • November 4, 2008 at 3:57 am

    #893505

    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

  • November 5, 2008 at 5:30 pm

    #894591

    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?

  • November 5, 2008 at 6:34 pm

    #894608

    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)
    Intro to Tally Tables and Functions

  • November 5, 2008 at 6:49 pm

    #894611

    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)
    Intro to Tally Tables and Functions

  • November 5, 2008 at 7:15 pm

    #894617

    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?

  • November 5, 2008 at 10:24 pm

    #894690

    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)
    Intro to Tally Tables and Functions

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

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