Solved

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

Posted on 2014-11-03
6
721 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2000: == Linked Server, With higher version == 16 103
Where clause in stored procedure 8 50
Passing value to a stored procedure 8 89
Restrict result set 1 33
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now