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, ',', '.')));
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
TRY_PARSE()), as @Remus explained. Here is an example using
TRY_PARSE() with the Spanish culture (
SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');
PARSE() documentation has the list of valid cultures.