Post reply

Rounding with a Decimal

  • September 3, 2014 at 10:26 am

    #298976

    Hello Everyone

    I am working with some money, which is always fun.

    I would like the

    DECLARE @MyPay decimal(5,9);

    SET @MyPay = 258.235543210;

    SELECT CAST(@MyPay AS decimal(5,2))

    This is what the resultset is currently with the code above:

    258.24

    I would like to Not have the value round up. I would like to always show only the first two digits to the right of the decimal and not perform any rounding.

    Can you suggest a clean and efficient way?

    Thank You in advance for your time, suggestion and code

    Andrew SQLDBA

  • September 3, 2014 at 10:34 am

    #1742900

    First of all, your variable data type should be decimal(12,9). To round down to significant digits, you'll need to multiple by factors of 10, for each significant digit, then divide by the same amount. As shown below:

    DECLARE @MyPay decimal(12,9);

    SET @MyPay = 258.235543210;

    SELECT CAST(FLOOR(100.0 * @MyPay) / 100.0 AS decimal(5,2))

    This result is: 258.23

  • September 3, 2014 at 10:43 am

    #1742906

    Quick solution, subtract the modulo of 0.01

    😎

    USE tempdb;

    GO

    DECLARE @MyPay DECIMAL(18,9);

    SET @MyPay = 258.235543210;

    SELECT @MyPay = @MyPay - @MyPay % 0.01

    SELECT @MyPay

    SELECT CAST(@MyPay AS decimal(5,2))

    Results

    ---------------------------------------

    258.230000000

    (1 row(s) affected)

    ---------------------------------------

    258.23

    (1 row(s) affected)

  • September 3, 2014 at 10:44 am

    #1742907

    Thank You Very much.

    That is exactly what I needed. I was not even close in my code.

    Thanks again

    Andrew SQLDBA

  • September 3, 2014 at 11:23 am

    #1742935

    Oh my, so much work when ROUND function has a truncation option.

    DECLARE @MyPay decimal(12,9);

    SET @MyPay = 258.235543210;

    SELECT ROUND(@MyPay,2,1)

    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

  • September 3, 2014 at 11:25 am

    #1742938

    Nice one Luis! I likes it.

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

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