Post reply

Trying to return simple results from a simple TSQL Query.

  • April 7, 2015 at 9:19 am

    #147320

    I am attempting to return results from a simple query in a comma separated format view (CSV).

    I have the following customer table contains the fields as defined as such:

    [Company] [nvarchar](50) NOT NULL,

    [AccountNumber] [nvarchar](20) NOT NULL,

    and

    [AccountBalance] [money] NOT NULL.

    When I run the following query:

    Select Company + ',' + [AccountNumber] + ',' + [AccountBalance]

    from customer

    where accountbalance = 0

    order by accountbalance

    I receive the following error:

    Msg 235, Level 16, State 0, Line 1

    Cannot convert a char value to money. The char value has incorrect syntax.

    Any assistance would be greatly appreciated.

    and Thank You

  • April 7, 2015 at 9:30 am

    #1789231

    you have to explicitly convert [AccountBalance] to append it to your string:

    Select Company + ',' + [AccountNumber] + ',' + CONVERT(varchar,[AccountBalance])

    from customer

    where accountbalance = 0

    order by accountbalance

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 7, 2015 at 9:39 am

    #1789235

    What if I need the precision to be to the nearest 0.0000? Do I need to add a cast statement too?

  • April 7, 2015 at 9:51 am

    #1789244

    convert has an optional third parameter for <style>, so when used on a money or smallmoney data type, you can get four digits to teh right with no commas, ie 4342.5645

    convert(varchar,moneyColumn,2)

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    Value Output

    0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

    1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

    2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

    126 Equivalent to style 2 when converting to char(n) or varchar(n)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 7, 2015 at 10:16 am

    #1789257

    Thanks Lowell.

    That did it!

    You rock,

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

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