[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1541
  • Last Modified:

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

  Please help me

Thanks.
0
towo2002
Asked:
towo2002
  • 5
  • 4
  • 3
  • +3
1 Solution
 
HilaireCommented:
This is a display problem and should be handled client-side.
Most programming langages provide extensive support for this kind of stuff.

0
 
danblakeCommented:
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
 
HilaireCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
billy21Commented:
or wait for the EU to change the official format to something sensible.
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
billy21Commented:
>>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
 
Anthony PerkinsCommented:
>>North America uses a comma in place of a decimal point?<<
Nope.  Misunderstood your comment.
0
 
Scott PletcherSenior 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
 
Scott PletcherSenior DBACommented:
You should probably avoid the overhead of a function if you can.
0
 
RedneckWonderlandCommented:
>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
 
RedneckWonderlandCommented:
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
 
RedneckWonderlandCommented:
Actually I also saw two Irish movies in there somewhere too but I think you get the idea.
0
 
Scott PletcherSenior DBACommented:
Indeed I do -- you like bad movies ( :-) ).
0
 
RedneckWonderlandCommented:
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
 
danblakeCommented:
:-)
0
 
RedneckWonderlandCommented:
Anyway, how many oscars did Lord of the Rings win again?...
0
 
Scott PletcherSenior 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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now