SQL Server 2005 - Converting to money type from Euro formatted text (which uses a comma decimal seperator)
Posted on 2014-11-03
I'm working on some number crunching which imports Amazon sales figures from our UK and French accounts.
The UK one works fine but the figures from French one are massively screwed up.
I've traced the problem to the currency conversion for the UK / French formats.
The UK currency format is (£) 4.21 (using a point separator)
The FR/Euro currency format is (€) 4,21 (using a comma separator)
I'm just using CAST(amount_text AS MONEY) to convert the text version to the Money type.
This works fine on the UK formatted values but on the above example, records 4,21 as 4,210 (four thousand, two hundred and ten).
How do I cast or convert this correctly?