April 7, 2015 at 9:19 am
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
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
April 7, 2015 at 9:39 am
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
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
April 7, 2015 at 10:16 am
Thanks Lowell.
That did it!
You rock,
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy