Round

  • pawel.sowka

    SSC Eights!

    Points: 851

    Comments posted to this topic are about the item Round

  • Hany Helmy

    SSChampion

    Points: 13435

    Didn`t know it will result in this, had 2 test it 2 make sure, thanx 4 the good question.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182369

    For fun

    😎

    SELECT ROUND(0.999,-1);

  • chgn01

    Hall of Fame

    Points: 3537

    How About

    SELECT ROUND($0.999,1)

    And

    SELECT ROUND(0.999,$3)

    ?

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • patricklambin

    SSCrazy Eights

    Points: 9964

    An easy question only because I have had the same question in an interview...

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    Looks a bit strange to me.

    SELECT ROUND(1.999,2) returns 2.000

    SELECT ROUND(10.999,2) returns 10.000

    but

    SELECT ROUND(0.999,2) won't work

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Arno Kwetters (9/14/2015)


    Looks a bit strange to me.

    SELECT ROUND(1.999,2) returns 2.000

    SELECT ROUND(10.999,2) returns 10.000

    but

    SELECT ROUND(0.999,2) won't work

    Probably because with 0.999 SQL Server doesn't care what comes before the decimal point since it's a zero.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    That can be the explanation, but it is still strange.

    A ROUND is a ROUND function

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

    select round(cast(0.999 as float),2) does the job. 🙂

  • Toreador

    SSChampion

    Points: 11243

    A good question, but the explanation isn't very helpful

  • matthew.flower

    Ten Centuries

    Points: 1381

    This is a serious fault in a basic function in SQL Server; my conclusion is based on the behaviour on SQL Server of:

    select ROUND(1.999, 2);

    2.000

    And Oracle 10g:

    SELECT ROUND(0.999,2) from dual;

    1

    And DB2

    select ROUND(0.999, 2) from SYSIBM.SYSDUMMY1;

    1.000

    Only SQL Server over many versions and many years fails to return a correct result and instead fails.

    This shouldn't be a trivia question but rather a serious question as to whether if SQL Server cannot do standard maths functions correctly it should be considered as a candidate tool for implementation by any organisation.

    Worse - this has been know about for years, and there are no possible detrimental impacts for correcting the fault, but still Microsoft has done nothing about it.

    It is failures like these that damage the reputation of companies such as the one I work for; we implement the same software on multiple database platforms and have to rely on standard sql functions to work consistently. We do test properly on the multiple platforms, but you can't test everything and why would our testing teams be looking for edge cases such as this one?

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    amen, Matthew

  • Ed Wagner

    SSC Guru

    Points: 286960

    This is one of those things to file away in the memory banks. I think it is a good lesson on defining your columns and variables with the right data type, including scale and precision, to cover your data. Thanks very much for a good question.

  • Luis Cazares

    SSC Guru

    Points: 183583

    I agree that the behavior of SQL Server is unexpected as it interprets the 0.999 as a decimal(3,3) which won't allow integers.

    However, if you're using this code, you deserve the error. No one should be rounding a literal value. Just type the rounded value. If it isn't the literal, there;s a data type involved and you should be able to predict the error.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 27 total)

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