Solved

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

Posted on 2014-11-03
6
677 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
Comment Utility
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
Comment Utility
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
Comment Utility
I believe it respects your current decimal notation only,
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Closing Comment

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

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
Agree - Format is good, but the subject says "SQL Server 2005", so I couldn't offer that as an option.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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