Round up or down IV

  • Comments posted to this topic are about the item Round up or down IV


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hi great qod!

    Here's a tip:

    Move the decimal point to the left by the negative number and then do a regular ROUND(d, 0). Applied to this it ends looking like ROUND(0.1666666,0) and there you have it.

    I wish I'd come up with it before I answered incorrectly 🙂

    Regards,

    Hrvoje Piasevoli

    Hrvoje Piasevoli

  • Again good question on same round function.Thanks hugo

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx

    for

    select ROUND(748.58, -3)

    result displayed is 1000.00

    but when i execute the same i get an error:

    An error occurred while executing batch. Error message is: Arithmetic Overflow.

    any suggestions plz. ?

  • ziangij (8/23/2010)


    plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx

    for

    select ROUND(748.58, -3)

    result displayed is 1000.00

    but when i execute the same i get an error:

    An error occurred while executing batch. Error message is: Arithmetic Overflow.

    any suggestions plz. ?

    Something with your connection options maybe. Are you running the default settings?

    The 748.58 is treated by SQL as Numeric(5,2) so 1000 doesnt fit there. With every setting at the default however SQL allows this. Now i havent been able to replicate the error. But you can check out

    http://www.eggheadcafe.com/software/aspnet/36185242/simple-round-call-results-in-arithmetic-overflow.aspx

    and maybe get a tip or 2.

  • Again, great question and great explanation.

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

  • ziangij (8/23/2010)


    plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx

    for

    select ROUND(748.58, -3)

    result displayed is 1000.00

    but when i execute the same i get an error:

    An error occurred while executing batch. Error message is: Arithmetic Overflow.

    any suggestions plz. ?

    I get the same error. The explanation is simple, and already given by tommyh: "748.58" is considered to be numeric(5,2), and the result of ROUND is the same data type as its input. But 1000.00 does not fit in a numeric(5,2).

    The fix is simple - use an explicit CAST to get a data type that does have some room for expansion:

    SELECT ROUND(CAST(748.58 AS decimal(6,2)), -3);

    tommyh (8/24/2010)


    With every setting at the default however SQL allows this. Now i havent been able to replicate the error.

    Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (8/24/2010)


    ziangij (8/23/2010)


    tommyh (8/24/2010)


    With every setting at the default however SQL allows this. Now i havent been able to replicate the error.

    Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

    8.00.760SP3Standard Edition

    9.00.1399.06RTMDeveloper Edition

    Though i dont think it matters. Since 99.9% off all the code i write is for SQL2000 (yeah old). I use the old Query Analyzer. If i try the same in SSMS (2005) i get the error (with same options set in both programs). So not a SQL version issue but an issue with the program issuing the commands it seams.

  • tommyh (8/24/2010)


    Hugo Kornelis (8/24/2010)


    ziangij (8/23/2010)


    tommyh (8/24/2010)


    With every setting at the default however SQL allows this. Now i havent been able to replicate the error.

    Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

    8.00.760SP3Standard Edition

    9.00.1399.06RTMDeveloper Edition

    Though i dont think it matters. Since 99.9% off all the code i write is for SQL2000 (yeah old). I use the old Query Analyzer. If i try the same in SSMS (2005) i get the error (with same options set in both programs). So not a SQL version issue but an issue with the program issuing the commands it seams.

    I don't have SA, but I tried using osql.exe and I also got 1000.00 as the result.

    I consider this a bug. The error you get in SSMS is actually the correct and expected behaviour. But somehow, for some weird reason, the evaluation of ROUND(748.58, -3) itself does not result in an error; instead the value of 1000.00 is "somehow" (don't ask me how!) encoded in the space available for a decimal(5,2). This is error number one.

    Error number two is client-specific. Apparently, QA and osql.exe will happily display a value if the server tells them to display the value 1000.00 with three positions before and two positions after the decimal place. SSMS behaves better - it knows this is impossible, and tells us about that.

    Here is an interesting experiment (works on every client):

    SELECT ROUND(789.98,-3) AS WeirdCol

    INTO WeirdTable;

    go

    EXEC sp_help 'WeirdTable';

    go

    UPDATE WeirdTable

    SET WeirdCol = WeirdCol - 0.001;

    go

    DROP TABLE WeirdTable;

    go


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!

  • Hugo Kornelis (8/24/2010)


    Here is an interesting experiment (works on every client):

    SELECT ROUND(789.98,-3) AS WeirdCol

    INTO WeirdTable;

    go

    EXEC sp_help 'WeirdTable';

    go

    UPDATE WeirdTable

    SET WeirdCol = WeirdCol - 0.001;

    go

    DROP TABLE WeirdTable;

    go

    Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though :cool:) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?

    Regards,

    Hrvoje Piasevoli

    Hrvoje Piasevoli

  • Hugo Kornelis (8/24/2010)


    ziangij (8/23/2010)


    plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx

    for

    select ROUND(748.58, -3)

    result displayed is 1000.00

    but when i execute the same i get an error:

    An error occurred while executing batch. Error message is: Arithmetic Overflow.

    any suggestions plz. ?

    I get the same error. The explanation is simple, and already given by tommyh: "748.58" is considered to be numeric(5,2), and the result of ROUND is the same data type as its input. But 1000.00 does not fit in a numeric(5,2).

    The fix is simple - use an explicit CAST to get a data type that does have some room for expansion:

    SELECT ROUND(CAST(748.58 AS decimal(6,2)), -3);

    tommyh (8/24/2010)


    With every setting at the default however SQL allows this. Now i havent been able to replicate the error.

    Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

    Microsoft SQL Server 2005 - 9.00.4294.00 (X64) Enterprise Edition (64-bit)

  • hrvoje.piasevoli (8/24/2010)


    Hugo Kornelis (8/24/2010)


    Here is an interesting experiment (works on every client):

    SELECT ROUND(789.98,-3) AS WeirdCol

    INTO WeirdTable;

    go

    EXEC sp_help 'WeirdTable';

    go

    UPDATE WeirdTable

    SET WeirdCol = WeirdCol - 0.001;

    go

    DROP TABLE WeirdTable;

    go

    Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though :cool:) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?

    Regards,

    Hrvoje Piasevoli

    yeah that's really strange... table is created..

    select queries gives an arithmetic overflow error...

  • Great QOD Hugo. Really makes you think.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Thanks Hugo. Great Question.

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

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