1

When my application - which I cannot change - tries to execute the following ad hoc statement:

Insert into Payment(idProduct,PaymentTypeId,total,paymentdate,cost) 
  VALUES ('48838','2','550',GETDATE(), '348,4') 

I get this error:

Error converting data type varchar to numeric.

This is because the cost column is a decimal, and the application is sending commas instead of decimals.

What configuration do I need to change? I tried changing default language to Spanish (advanced properties for the server) and the login to Spanish too, but it is not working. The language property in the server is English and I can't figure out how to change it.

2

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

0

Use PARSE and specify the appropriate culture.

  • PARSE() did not exist in SQL Server 2008 R2. – Aaron Bertrand Nov 16 '15 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.