Solved

How to convert a number to currency?

Posted on 2004-04-01
18
1,475 Views
Last Modified: 2008-03-04
Dear all experts,

  I'm living in Europe and I would like to know on how to convert a number to currency in Europe format .  The format should be like this:

  123456 -> 123.456,00
  123456.78 -> 123.456,78

  As you may know that the format of currency in Europe is difference to the others, I know i can use :

  Select Convert (varchar(20), CAST ('123456' as money), 1) to convert it as money format, however it only returns 123,456
  which is not the Europe currency format .   I want it to become 123.456.00

  Please help me

Thanks.
0
Comment
Question by:towo2002
  • 5
  • 4
  • 3
  • +3
18 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 10730709
This is a display problem and should be handled client-side.
Most programming langages provide extensive support for this kind of stuff.

0
 
LVL 13

Expert Comment

by:danblake
ID: 10730782
Depending on your application (if it is an ODBC or OLEDB application) you can use the regional ouput settings when outputing data within the ODBC/OLE DB connection.  This does not help if you are using QA for your connection to the database, but allows the standard regional outputs (dependant on language being used on each PC) to be used for the application allowing a multi-display output system.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 100 total points
ID: 10730820
Here's the kind of logic that could be use to format a string on the SQL Server side.
Code below provided for the sake of demonstration

I'd never use it on my own db.
You could afford using it on select statement processing very few rows, but on a query fetching many rows, it will result in a major performance loss

Again, most programming langages provide formatting functions using excel-like formatstrings
eg FormatNumber(yourAmount, "0#000,00")


create function ufn_format_curr(@amount money) returns varchar(20) as
begin
declare @amnt bigint, @str varchar(20)
set @amnt = cast(@amount*100 as bigint)
select @str = ',' + right('00' + cast(@amnt%100 as varchar(2)),2), @amnt = @amnt / 100
while @amnt > 0
      select @str =  '.' + cast(@amnt%1000 as varchar(3)) + @str, @amnt = @amnt / 1000
return(case left(@str,1) when '.' then right(@str, len(@str)-1) else '0' + @str end)
end
go


select dbo.ufn_format_curr(123456.78)

HTH

Hilaire
0
 
LVL 6

Expert Comment

by:billy21
ID: 10731051
or wait for the EU to change the official format to something sensible.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10732243
>>or wait for the EU to change the official format to something sensible.<<
I would not hold your breath.  The vast majority follow this standard, not just EU.  You could say the same for North America.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10732260
towo2002,

Please maintain your old open questions:
1 02/27/2004 100 Wake on Lan (or Wake on Wan)  Open Networking
2 09/10/2003 50 Can anyone tells me how can I export my ...  Open Microsoft SQL Server
3 09/11/2003 100 How to handle error in DTS package?  Open Microsoft SQL Server
4 10/29/2003 100 is there any method that can return "1st...  Open Microsoft SQL Server

Thanks.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10732281
>>or wait for the EU to change the official format to something sensible.<<
I would not hold your breath.  The vast majority follow this standard, not just EU.  You could say the same for North America.


North America uses a comma in place of a decimal point?

Wouldn't surprise me actually as they haven't even properly moved to the metric system yet.  They're miles behind the rest of the world when it comes to standards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10732362
>>North America uses a comma in place of a decimal point?<<
Nope.  Misunderstood your comment.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10732565
>> 123456.78 -> 123.456,78 <<

Since:
SELECT CONVERT(CHAR(30), CAST('123456.78' AS SMALLMONEY), 1) --> 123,456.78

Then:
SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(30), CAST('123456.78' AS SMALLMONEY), 1), '.', '~'), ',', '.'), '~', ',') --> 123.456,78


Btw:
>> They're miles behind the rest of the world when it comes to standards <<

For metric system, yeah.  As the saying goes, old habits die hard.
But when it comes to almost anything else, we set the standards for the world.  For example, is your PC's OS:
Windows (US); Apple/Mac-type (US); UNIX (US); Linux (not US, but based on UNIX)?
Origin of relational dbs: US (IBM).  of SQL language: US (IBM).  Most hard drive improvements: US (mostly IBM).
The last time you went to a movie, which country produced it?  What about the movie before that?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10732573
You should probably avoid the overhead of a function if you can.
0
 

Expert Comment

by:RedneckWonderland
ID: 10732666
>For metric system, yeah.  As the saying goes, old habits die hard.
Yep but every other country who converted had old habbits too

But when it comes to almost anything else, we set the standards for the world.  For example, is your PC's OS:
Windows (US); Apple/Mac-type (US); UNIX (US); Linux (not US, but based on UNIX)?
Yep but I don't see the relevance

Origin of relational dbs: US (IBM).  of SQL language: US (IBM).  Most hard drive improvements: US (mostly IBM).
Yes

The last time you went to a movie, which country produced it?  What about the movie before that?
Britain
Britain
Australia
New Zealand
New Zealand
New Zealand (yep saw the whole trilogy in one night)
Australia
China
Britain
China

Can't remember the last american film I saw... hmmm would have been Star Wars 2.
0
 

Expert Comment

by:RedneckWonderland
ID: 10732701
yep was star wars 2.  American production Made in Australia just like all of the matrix movies and a lot of other US productions.
0
 

Expert Comment

by:RedneckWonderland
ID: 10732816
Actually I also saw two Irish movies in there somewhere too but I think you get the idea.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10732900
Indeed I do -- you like bad movies ( :-) ).
0
 

Expert Comment

by:RedneckWonderland
ID: 10732917
You need a few IQ points extracted to enjoy an american movie.  Very few appeal to me.  But for British movies a seditive helps.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10732920
:-)
0
 

Expert Comment

by:RedneckWonderland
ID: 10733013
Anyway, how many oscars did Lord of the Rings win again?...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10733346
Lots, but that was a joint USA/New Zealand project.

The other producers (other than Jackson) were American.

http://www.imdb.com/title/tt0167260/


That's why the Oscars have a "Best Foreign Film" category -- so the foreign films have a chance to win something.  It was getting embarrassing (to some).

If you'd like, we can continue this in the Lounge ... don't want to "pollute" this thread anymore.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

12 Experts available now in Live!

Get 1:1 Help Now