Error converting data type varchar to numeric

Question

I have this part of query that causes the above error:

CONVERT(varchar(15),CAST((AmountOfInsurance) as MONEY),1)

What am I doing worng?

This is the declararion of AmountOfInsurance

AmountOfInsurance decimal(19,2),

Error message pretty clear. You cannot convert non numeric characters to money. `AmountOfInsurance` has some non numeric characters — Pரதீப், Feb 02 '17 at 07:08
can you please post the whole query, from query you are fetching varchar and on code side you have assigned numeric so you are getting above error — Chintan Udeshi, Feb 02 '17 at 07:09
there is non decimal value as AmountOfInsurance .if AmountOfInsurance is decimal or its is null also work fine . — Ronak Patel, Feb 02 '17 at 07:11
Why not directly convert `AmountOfInsurance` to VARCHAR, WHy you need to convert it to maney and then in VARCHAR format? — Jaydip Jadhav, Feb 02 '17 at 07:13
converting to money first will format the number with thousand-seperator, maybe that's the intention — CeOnSql, Feb 02 '17 at 07:14

score 1 · Accepted Answer · edited Feb 02 '17 at 07:22

1

I hope this will work for you...

CONVERT(varchar(15), CONVERT(money, AmountOfInsurance), 1)

More information: the last parameter decides what the output format looks like:

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.

If you want to truncate the pennies, and count in pounds, you can use rounding to the nearest pound, floor to the lowest whole pound, or ceiling to round up the pounds

edited Feb 02 '17 at 07:22

marc_s

732,580
175
1,330
1,459

answered Feb 02 '17 at 07:15

Darshak

859
7
18

[This is plagiarism](http://stackoverflow.com/a/532531/295783). Please do NOT copy word for word from other answers but instead comment that they are duplicates. – mplungjan Feb 03 '17 at 13:43
@mplungjan, Lots of posts mention that http://stackoverflow.com/search?q=0+%28default%29+No+commas+every+three+digits+to+the+left+of+the+decimal+point%2C+and+two+digits+to+the+right+of+the+decimal+point%3B+for+example%2C+4235.98.+ .... Looks like it is all plagiarized from a single source. – Bhargav Rao Feb 03 '17 at 13:49
This is the second copy of a post I have seen from this user – mplungjan Feb 03 '17 at 13:54
@mplungjan i accept my apology for that..now onwards i will take care this things.. – Darshak Feb 03 '17 at 14:47

Error converting data type varchar to numeric

1 Answers1