Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Hidden Formatting Troubles with STR() (SQL Spackle) RE: Hidden Formatting Troubles with STR() (SQL Spackle)

  • December 15, 2010 at 8:52 am

    #1262935

    rmechaber (12/15/2010)

    Thanks Jeff, for your customary focused, useful style and content!

    <math_complaint>

    Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value of the same type as its passed argument. In your examples under "Incorrect Rounding", note that all of the returned values using ROUND(SomeNumber, 2) return 3 decimals.

    In junior high school, if I answered the test question "Round off 0.325 to 2 decimal places" with "0.330" I would have gotten that one wrong. Significant digits? What's that? That trailing zero means something in science, and its inclusion here is, IMHO, incorrect. The correct answer is 0.33.

    </math_complaint>

    Any thoughts on this?

    Rich

    Thanks for the feedback. You're one of the few to pick up on the fact that the explicit ROUNDing to two decimal places didn't actually change the underlying datatype nor format the output. And, I agree... it's one of those things that just isn't expected.

    For those that didn't notice, ROUND didn't and doesn't do any formatting or conversion of the underlying datatype. It simply does what it says... it Rounds to a given number of decimal places (or whole number places if you use a minus-length). Because of the original multiplication by 1/1000th, the underlying datatype has 3 decimal places and ROUND doesn't change that. The number is simply rounding with no change in format and no change in datatype.

    --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)