Post reply

Show data upto 2 decimal places but with exact values

  • February 16, 2008 at 4:40 am

    #378539

    Hi all,

    I would like to show the data upto 2 decimal places but without rounding off the integers. If the number is 4.6758 then data retrieved should be 4.67 and not 4.68.

    I tried round, decimal, cast, etc. but each function converts the value to 4.68.

    Can anybody let me know the syntax so that I get the exact values after decimal.

    Best Regards,

    Hemant.:)

  • February 16, 2008 at 6:32 am

    #779096

    You have FLOOR which rounds down and CEILING which rounds up but it doesn't specify decimal places.

    You could do something like

    FLOOR(Value *100)/100

    Depending on what you are doing with your rounded figures and where you are displaying them I would recommend that you don't do presentation stuff in the DB layer unless you absolutely have to.

    LinkedIn Profile

  • February 16, 2008 at 8:11 am

    #779100

    Would this help?

    declare @var varchar(10), @int dec(4,3)

    set @int=4.678

    set @var = substring(cast(@int as varchar(5)),1, 4)

    select 'Number is '+ cast(@int as varchar(5)) + ' Character is ' + @var

    Toni

  • February 16, 2008 at 8:13 am

    #779101

    You can also use the "3rd operand" of round which identifies whether the number should be rounded or truncated (as you've asked for)... the information for the 3rd operand is in Books Online but, basically, if it's present and anything other than 0, it will cause ROUND to truncate rather than round...

    [font="Courier New"]SELECT ROUND(4.6758,2,1) --3rd operand is a non-zero which means TRUNCATE [/font]

    If you need this for some sort of display purpose (should be done in the GUI, if one is available), you can also do this... includes right hand justification....

    [font="Courier New"]SELECT STR(ROUND(4.6758,2,1),10,2)[/font]

    --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)

  • February 17, 2008 at 6:39 am

    #779171

    I try to avoid string manipulation where possible/practical as it computers work better with numbers. Sometimes string manipulation is a necessary evil.

    LinkedIn Profile

  • February 17, 2008 at 8:21 am

    #779179

    Yes, I absolutely agree... any type of formatting should be done only in the GUI... if there is one...

    But the keyword in the title of the thread is "Show"... at that point, it usually means final output formatting for either a QA screen display or a printout and the number won't be used any further. Could also be converted to a Decimal using either of the following forumulii...

    [font="Courier New"]

    SELECT CAST(ROUND(4.6758,2,1) AS DECIMAL (9,2)) -- Mine formatted using Decimal

    SELECT CAST(FLOOR(4.6758*100)/100 AS DECIMAL (9,2)) --David's formatted using Decimal[/font]

    ... Heh... but if you're gonna go through all that for a formatted display (if "Show" really does mean "DISPLAY" in this case), then why not right justify making the decimals align and having a pretty output?

    [font="Courier New"]SELECT STR(ROUND(4.6758,2,1),10,2) [/font] --Right justifies

    --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)

  • February 19, 2008 at 2:55 am

    #779624

    Thanks Jeff...

  • February 19, 2008 at 10:32 am

    #779828

    You bet, Sengar... thank you for the feedback.

    It would be interesting to know, though... just why do you need to force a format of 2 decimal places with truncation instead of rounding? Are you doing calculations with it or display only or both? If display is involved, why aren't you doing it in the GUI?

    Again, I (we) just like to know these types of things for future reference... thanks.

    --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)

  • October 31, 2016 at 10:48 pm

    #1909782

    Hi all,

    I would like to show the data up to 2 decimal places but without rounding off the integers. If the number is 4.6758 then data retrieved should be 4.67 and not 4.68.

    I tried round, decimal, cast, etc. but each function converts the value to 4.68.

    Can anybody let me know the syntax so that I get the exact values after decimal.

    Best Regards,

    Htet Htet

  • November 1, 2016 at 1:46 pm

    #1909996

    snowprincess42073 (10/31/2016)

    Hi all,

    I would like to show the data up to 2 decimal places but without rounding off the integers. If the number is 4.6758 then data retrieved should be 4.67 and not 4.68.

    I tried round, decimal, cast, etc. but each function converts the value to 4.68.

    Can anybody let me know the syntax so that I get the exact values after decimal.

    Best Regards,

    Htet Htet

    That's actually referred to as "truncation". See the following code for a possible solution.

    SELECT ROUND(4.6758,2,1) --Non-zero 3rd operand of ROUND does "truncation" rather than "rounding"

    ,CONVERT(DECIMAL(9,2),ROUND(4.6758,2,1)) --CAST or CONVERT to DECIMAL for display purposes.

    --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)

  • November 1, 2016 at 2:17 pm

    #1910001

    snowprincess42073 (10/31/2016)

    Hi all,

    I would like to show the data up to 2 decimal places but without rounding off the integers. If the number is 4.6758 then data retrieved should be 4.67 and not 4.68.

    I tried round, decimal, cast, etc. but each function converts the value to 4.68.

    Can anybody let me know the syntax so that I get the exact values after decimal.

    Best Regards,

    Htet Htet

    First, this thread is almost a decade old.

    Second, your post is an exact copy of the original post in this thread except for the name.

    Please do not resurrect old threads unless you have something valuable to contribute.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 1, 2016 at 2:56 pm

    #1910005

    drew.allen (11/1/2016)

    snowprincess42073 (10/31/2016)

    Hi all,

    I would like to show the data up to 2 decimal places but without rounding off the integers. If the number is 4.6758 then data retrieved should be 4.67 and not 4.68.

    I tried round, decimal, cast, etc. but each function converts the value to 4.68.

    Can anybody let me know the syntax so that I get the exact values after decimal.

    Best Regards,

    Htet Htet

    First, this thread is almost a decade old.

    Second, your post is an exact copy of the original post in this thread except for the name.

    Please do not resurrect old threads unless you have something valuable to contribute.

    Drew

    Lighten up, Drew. This isn't stackoverflow... 😉

    --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 12 posts - 1 through 11 (of 11 total)

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