Trying to return simple results from a simple TSQL Query.

• Greg.Jackson

  Hall of Fame

  Points: 3256

  More actions

  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

• Lowell

  SSC Guru

  Points: 323527

  More actions

  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!

• Greg.Jackson

  Hall of Fame

  Points: 3256

  More actions

  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?

• Lowell

  SSC Guru

  Points: 323527

  More actions

  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!

• Greg.Jackson

  Hall of Fame

  Points: 3256

  More actions

  April 7, 2015 at 10:16 am

  #1789257

  Thanks Lowell.

  That did it!

  You rock,