Solved

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

Posted on 2014-11-03
6
763 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 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