Post reply

sqlcmd numeric formatting numbers with thousands format i.e 1,234.56 I want 1234.56

  • December 15, 2009 at 6:15 am

    #96360

    When I run the query outside of sqlcmd I get 1234.56. When trapping output from sqlcmd I get 1,234.56

    Please help as the output is a CSV file and as such is corrupted by the formatting of the thousands separator.

    Please note that the number is outputted in the following format.

    cast (tax_amount-vat_amount as numeric(18,2))

    I did it like this to stop sqlcmd padding numbers with leading zeroes. Yet another annoying habit!

  • December 15, 2009 at 6:24 am

    #1092460

    Are you using the "-R" argument of SqlCmd?

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

  • December 16, 2009 at 2:27 am

    #1092868

    Yes that fixed it. Many thanks.

    Where can I read up on the effects of taking the -R off.

    How do I change the regional settings in my SQL Server i.e. remove the , in thousands?

    Thanks again

  • December 16, 2009 at 5:56 am

    #1092942

    Kelvin Phayre (12/16/2009)

    Yes that fixed it. Many thanks.

    Where can I read up on the effects of taking the -R off.

    How do I change the regional settings in my SQL Server i.e. remove the , in thousands?

    Thanks again

    You can read all about the various switches for SQLCMD in Books Online (the help system that comes with SQL Server). You need to look in Windows Help to figure out how to change the system Regional Settings.

    However, I'd be tempted to just leave the -R turned off and write the query to do the fixed formatting.

    --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 4 posts - 1 through 3 (of 3 total)

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