Retuning Fomated number from a query

  • Hi to all,

     

    I am not sure this is where I post this question.

     

    I trying to find a way to return the results from this query.

     

    I have column A value = ‘1.6025’

     

    How can I return it from the table as “1.60”

     

    Select A from TableName

     

    Results

    --------

    1.6025

     

    this is what I need

    Results

    --------

    1.60

     

    Thanks

    Chaz


    Kindest Regards,

    Chaz

  • round()?

    declare @i numeric( 6, 4)

    select @i = 1.6025

    select @i, round(@i, 2)

  • I’ve tried that but it is not working.

     

    Here is how it looks in the table

     

    ColA     ColB    ColC   ColD

    --------   -------- -------  ------

    432855  18893  3601   19.060000

     

    This is how I need it to look after using a select statement

     

    ColA       ColB     ColC    ColD

    ---------   ---------  -------   ------

    432,855  18,893   3,601   19.06

     

    Is there a function in SQL like numberformat(), or anything that will format numbers?

     

    thanks for your respond

     

     

     

     

     


    Kindest Regards,

    Chaz

  • I believe the closest you can come in SQL Server (this is something that is normally left to be done on the front-end), is to use MONEY.

    SELECT CONVERT(MONEY, ColA, 1) AS ColA,

    CONVERT(MONEY, ColB, 1) AS ColB,

    CONVERT(MONEY, ColC, 1) AS ColC,

    CONVERT(MONEY, ColD, 1) AS ColD

    FROM tablename

    -SQLBill

  • Thanks SQLBill

    This did not give me what I am looking for.

    I'm going to dump the results in excel and format it that way for a quick fix.

    I was trying to look for a quicker way to get the results to the user.

    Thanks


    Kindest Regards,

    Chaz

  • Try using the STR function such as this:

    declare @i numeric( 6, 4)

    select @i = 1.6075

    select @i, str(@i, 6, 2)

  • Thanks ericwallace,

    that works fine, now how do i add the ',' to it.

    i.e.

    2794

    2,794

     


    Kindest Regards,

    Chaz

  • Hi Chaz,

    I finally came across the answer to this question.  Don't know if it is too late or not, but here it is:

    declare @i numeric( 12, 4)

    select @i = 6582321.6075

    SELECT CONVERT(varchar,CAST(@i AS money),1)

    This returns result of  6,582,321.61

    I did not realize that money is actually similar to format of decimal(19,4) and to get the style you want you have to CONVERT the money to character format with style 1 after you have CAST the numeric as money.  It makes perfect sense after you understand all the details.

    Eric

  • SELECT

    REPLACE(CONVERT(varchar,CAST(ColA as money),1),'.00',''),

    REPLACE(CONVERT(varchar,CAST(ColB as money),1),'.00',''),

    REPLACE(CONVERT(varchar,CAST(ColC as money),1),'.00',''),

    CONVERT(varchar,CAST(ROUND(ColD,2,1) as money),1)

    FROM [TableName]

    Beware of rounding

    SELECT CONVERT(varchar,CAST(1.6075 as money),1)

    Result: 1.61

    SELECT CONVERT(varchar,CAST(ROUND(1.6075,2,1) as money),1)

    Result: 1.60

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David,

    Good suggestion on eliminating the .00 on items with no pennies, but I believe he wants it to round as opposed to truncating, which the convert will do without using round.

    Eric

Viewing 10 posts - 1 through 9 (of 9 total)

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