Post reply

Format Query Value

  • January 12, 2007 at 4:25 am

    #193845

    I having difficulty formating a a query result to display the value in the correct format. The output is a number such as 1234567. I need the output format to be 1,234,567. I'm just haveing a BF and can't get it to work. Any pointers would be appreciated.

    CY

  • January 12, 2007 at 4:54 am

    #682855

    Number formatting shouldn't really be done in the Database ; it should be done by the front end app.  If there is no option but to format the number using T-SQL , you could use CAST and CONVERT, like this:

    DECLARE @number FLOAT

    SET @number = 12124.23

    SELECT convert(varchar,cast(@number as money),1) 

    Hope that helps.

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • January 12, 2007 at 5:20 am

    #682864

    Thanks for the reply...I wish I could. I'm running a query that is emailed. I would like to format that number column into standard form. The column is an integer and I'm not having success converting...I will not give in to this....

  • January 12, 2007 at 5:36 am

    #682870

    It is pretty nasty thing to do it on SQL Server, but if it is really necessary, here you go:

    DECLARE @value INT

    SET @value = 1234567

    SELECT  @value as orig_value,

     CONVERT(VARCHAR(20), CONVERT(money,@value),1) as with_decimal,

     LEFT(CONVERT(VARCHAR(20), CONVERT(money,@value),1), LEN(CONVERT(VARCHAR(20), CONVERT(money,@value),1))-3) as without_decimal

    As you see, it isn't too bad if the result can have 2 decimal places. If you want to display no decimal places, you have to zap last 3 characters from the result.

  • January 12, 2007 at 5:46 am

    #682873

    Works like a charm. Thanks!

    CY

Viewing 5 posts - 1 through 5 (of 5 total)

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