Post reply

Truncating decimals without rounding

  • May 14, 2007 at 3:30 pm

    #172380

    Hello,

    I have a data feed that presents decimals out to the 9th decimal place.  When I use the function:

    Convert(decimal(12,4), IncomingValue)

    The result is rounded up or down at the 4th decimal position instead of simply being truncated at the 4th spot.

    Is there a math function which will do this truncation of the "extra" places, or do I have to convert to strings and then do padding and other manipulations to get the desired result?

    thanks,

    Elliott

  • May 14, 2007 at 4:07 pm

    #706356

    OK, I got it.  The FLOOR function will eliminate the "extra" digits:

    Declare @num decimal(18,9)

    Set @num=99.021377000  -- goal is to eliminate last 5 digits

    Set @num=@num * 10000  -- put the decimal at the last desired digit

    Set @num=FLOOR(@num)  -- truncates the fractional digits

    Set @num=@num/10000  -- return the decimal to it's correct position

    Select convert(decimal(12,4),@num) returns 99.0213

    I suppose I better go write a function to do this.

     

    Elliott

  • May 14, 2007 at 4:51 pm

    #706359

    The ROUND function can also truncate.  ROUND(@num, 4, 1) will do the job without the extra scaling.

    PRINT

    ROUND(99.021377000, 4, 0)

    99.021400000

    PRINT

    ROUND(99.021377000, 4, 1)

    99.021300000

  • May 16, 2007 at 12:01 pm

    #706909

    Excellent!

    I didn't know about this option in the ROUND function. 

    Plunk!!  (that's the sound of my UDF hitting the wastebasket)

    Thanks, Elliott

Viewing 4 posts - 1 through 3 (of 3 total)

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