# How to convert a number to currency?

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

Thanks.
LVL 1
###### Who is Participating?

Commented:
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

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

0

Commented:
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

Commented:
or wait for the EU to change the official format to something sensible.
0

Commented:
>>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

Commented:
towo2002,

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

Commented:
>>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

Commented:
>>North America uses a comma in place of a decimal point?<<
0

Senior DBACommented:
>> 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

Senior DBACommented:
You should probably avoid the overhead of a function if you can.
0

Commented:
>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

Commented:
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

Commented:
Actually I also saw two Irish movies in there somewhere too but I think you get the idea.
0

Senior DBACommented:
Indeed I do -- you like bad movies ( :-) ).
0

Commented:
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

Commented:
:-)
0

Commented:
Anyway, how many oscars did Lord of the Rings win again?...
0

Senior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.