Post reply

FORMAT output to two Deciaml Places

  • July 20, 2009 at 12:48 am

    #136367

    Hi, I have the following line of code in a stored procedure:

    ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0') AS Quantity

    I would like to modify to also format to 2 decimal places. Currently if the Quantity is 9 is displays as 9.00000.

    The data type on the underlying table is DECIMAL 17,5.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • July 20, 2009 at 12:59 am

    #1026767

    Since you're formatting data, let's throw in right-justification, as well...

    STR(ISNULL(SUM(QuantityOutstanding),0),10,2) AS Quantity

    The key is that this type of formatting should be left up to the GUI, if you have one, so that any local currency settings can take affect.

    ... and I got a real charge out of your jokes 😛

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

  • July 20, 2009 at 1:02 am

    #1026770

    CHECK THIS

    SELECT CONVERT(DECIMAL(17,2),9)

  • July 20, 2009 at 1:54 am

    #1026785

    Thanks guys. Jeff I am using Visual Web Developer 2008. I tried omitting the formatting from the T-SQL then on the column within the GridView in VWD I set ConvertEmptyStringToNull with display text of 0. I then then set DataFormatString value {0} but it still displayed as 9.00000 (also tried {0:d}.

    Thanks,

    Phil.

    Update: It transpires that some stock values may be 0.003 (Units of measure Metres).

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

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

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