Solved

SQL Server 2005 - Converting to money type from Euro formatted text (which uses a comma decimal seperator)

Posted on 2014-11-03
6
890 Views
Last Modified: 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?
0
Comment
Question by:Beamson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40419123
If you know which amounts are in the French format, try:

CAST(Replace(Replace(Replace(amount_text,'.','#'),',','.'),'#',',') as money)
0
 
LVL 1

Author Comment

by:Beamson
ID: 40419170
Thanks - I was hoping to avoid this but it will work.
Is there not an explicit Cast or Convert function which recognises this kind of input string?

I have a similar problem with date formats but can switch between
CONVERT( smalldatetime, transaction_date_text ) and
CONVERT( smalldatetime, transaction_date_text, 104 ) for the two formats.
In the date section there seems to be fairly good coverage of most possible date types.

Is there a CONVERT (money) equivalent?
The optional style switches which I've come across are limited to the handful of non-EU formats.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419178
I believe it respects your current decimal notation only,
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Closing Comment

by:Beamson
ID: 40419187
Its not pretty but it looks like its the least-worst way!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40419192
If you are using SQL 2012 or later there are "styles" for currency through the FORMAT() function
but not to CAST() or CONVERT()
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419194
Agree - Format is good, but the subject says "SQL Server 2005", so I couldn't offer that as an option.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question