Post reply

Decimals and formatting

  • August 8, 2005 at 9:32 am

    #87084

    I've searched through the forums but have not found anything specifically related to my topic.

    If you take the following SQL statement and run it the result is .480

    SELECT CONVERT(DECIMAL(10, 3), 0.4799608993157)

    I want the result to be .479. Is there a way to achieve .479 as a result?

    Thanks,

    Kyle

  • August 8, 2005 at 10:10 am

    #580466

    select convert(decimal(10,3),convert(int, (0.4799608993157 * 1000))) / 1000

    And then again, I might be wrong ...
    David Webb

  • August 8, 2005 at 11:07 am

    #580479

    Here's another way that might be faster as it's not doing the CONVERTs

    SELECT floor(0.4799608993157 * 1000) / 1000

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 8, 2005 at 11:22 am

    #580485

    Another poster supplied the following script.

    Sorry cant find the thread to give credit.

     

     

    CREATE FUNCTION RemoveDecimalsWithoutRounding

    ( @InputValue Float

    , @Decimals Int

    )

    -- Get the rounded value

    DECLARE @ReturnValue Float;

    SET @ReturnValue = Round(@InputValue, @Decimals);

    -- If the value is too high remove the smalles value allowed by the specified number of decimal places

    IF @ReturnValue > @InputValue

     SET @ReturnValue = @ReturnValue - (1 /  Power(Convert(Float, 10), Convert(Float, @Decimals)));

    PRINT @ReturnValue;

    Modify as you wish for in datatype.

  • August 8, 2005 at 9:16 pm

    #580582

    SELECT ROUND(0.4799608993157, 3, 1)

  • August 8, 2005 at 9:34 pm

    #580584

    Thanks everyone! I knew I would get some good solutions here.

    Kyle

  • August 9, 2005 at 6:53 am

    #580687

    SELECT  CONVERT(DECIMAL(10, 3),ROUND(0.4799608993157, 3, 1))

     

    This will give result as .479

  • August 9, 2005 at 8:01 am

    #580728

    Why do the CONVERT when the ROUND works without it?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 9, 2005 at 11:36 am

    #580821

    SELECT  ROUND(0.4799608993157, 3, 1) will return result as

    .4790000000000

     

    SELECT  CONVERT(DECIMAL(10, 3),ROUND(0.4799608993157, 3, 1)) will return result as

    .479

     

    Thanks,

    Murthy

  • August 9, 2005 at 1:12 pm

    #580840

    To Murthy: You are right, but what is the difference between .4790000000000 and .479 for SQL Server?

    SELECT CONVERT(DECIMAL(10, 3),ROUND(0.4799608993157, 3, 1)) / 1 -- .479000

    It is the client duty to represent these numbers in correct way for the end user.

  • September 26, 2007 at 12:28 pm

    #736495

    Will this code work in T-SQL or will I have to modify it

    When I run it it I get the following error

    Msg 156, Level 15, State 1, Procedure RemoveDecimalsWithoutRounding, Line 7

    Incorrect syntax near the keyword 'DECLARE'.

    Thanks

  • September 26, 2007 at 2:53 pm

    #736535

    Most likely a small typo. Can you post the code you are using and the exact error message? That'll help us figure this out quicker for you.

  • October 2, 2007 at 1:04 am

    #738028

    The function was posted above by Ray.

    CREATE FUNCTION RemoveDecimalsWithoutRounding ( @InputValue Float, @Decimals Int)

    -- Get the rounded value

    DECLARE @ReturnValue Float;

    SET @ReturnValue = Round(@InputValue, @Decimals);

    -- If the value is too high remove the smalles value allowed by the specified number of decimal places

    IF @ReturnValue > @InputValue

    SET @ReturnValue = @ReturnValue - (1 / Power(Convert(Float, 10), Convert(Float, @Decimals)));

    PRINT @ReturnValue;

    What I can see is this:

    - a function needs to start with

    CREATE FUNCTION RemoveDecimalsWithoutRounding

    ( @InputValue Float, @Decimals Int)

    RETURNS FLOAT

    AS

    BEGIN

    /*body of the function here*/

    RETURN @ReturnValue

    END

    Also, better remove all the semicolons (";") - they don't belong into a SQL function.

    I didn't check the function itself, just what was apparent at first glance.

  • October 6, 2007 at 1:44 pm

    #739811

    Oh, thank goodness... it's not "Bankers Rounding" 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

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