Round at specific decimal point

  • giszzmo

    Mr or Mrs. 500

    Points: 586

    Hi, I am trying to round my numbers up to the nearest whole number if the decimal is equal to or greater than .7, for example 28.7 will be 29, or round down if it is below .7, for example, 28.6 will be 28.  I'm not sure If I need to truncate first and make a temp table or if there is a function that will do this.  If someone could point me in the right direction. Thanks!

  • gilbert delarosa

    Old Hand

    Points: 392

    this should do it. maybe make it into a Funtion

    DECLARE 
    @TestNumber decimal(4,1) = 28.7
    ,@PreferredDecimal decimal(4,1)= 0.7
    ;

    SELECT
    CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END
    ;

    SET @TestNumber = 28.6;

    SELECT
    CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END
    ;
  • Phil Parkin

    SSC Guru

    Points: 243762

    Will this work?

    SELECT ROUND(@TestNumber - 0.2, 0);

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Phil Parkin

    SSC Guru

    Points: 243762

    Alternatively

    SELECT ROUND(@TestNumber + 0.3, 0, 1);

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • giszzmo

    Mr or Mrs. 500

    Points: 586

    Didn't work, but thanks.

  • Phil Parkin

    SSC Guru

    Points: 243762

    giszzmo wrote:

    Didn't work, but thanks.

    Here's my test script.

    DECLARE @i DECIMAL(5, 3) = 0;
    DECLARE @Inc DECIMAL(3, 2) = 0.01;

    WHILE @i < 2
    BEGIN
    PRINT CONCAT(@i, ', ', ROUND(@i + 0.3, 0, 1));

    SET @i = @i + @Inc;
    END;

    Now please show me yours.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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