Money data presenting

Question

Post reply

Money data presenting

Yuri55

SSCrazy Eights

Points: 8449
More actions
July 12, 2005 at 12:21 pm

#90154

Hi all,
Need some help with presenting decimal/money data type using related currency symbol (like $ for USD). Let say, we have table UserEarnings (UserId- Amount) and table UserCurrency (UserId-CurrencyId). Certainly, I can create UDF to show $1.2 for guys in US, £3.4- in GB (and etc) but probably there is more elegant way. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

what is the problem with joining the tables ?

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Or even better, do it client side. Let the programmers earn they buck .

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 3

Sorry, probably I wasn’t clear in my initial post. Data (Amount) for every user is in numeric format (i.e. like 12.34 regardless where, I mean country, every user is). I would like to present this data in country specific currency format with preceding currency symbol (e.g. $12.34 for guys from US or £12.34 from GB). To find related symbol I will use second mentioned table with currencyid or countryid. But for some countries currency symbol it’s not everything, I mean sometimes use comma, sometimes dot to separate …, etc. Therefore, I thought may be TSQL has a feature to present numeric data in country specific money format.

To Remi: you are absolutely right- if there is no elegant and simple way to do this in TSQL we will move everything to clients. Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Use the local parameter settings on the clients... That'll present the data as they wish (by itself if using excel of access). Simpler and makes less work for the server. Make sure that the data is shifted in numeric form between the server and the clients and not strings, as this could cause many sorts of problems.

noeld SSC Guru Points: 96590 More actions · Answer 5

Yuri,

There is a way (but is risky in the sense that your app should have been designed from the ground up to handle it )

at begining of the connection just run

set language = British

or

set language = Español -- depending on the User

and your currencies (money) data types, your dates and your numeric format will go across using that setting and what is even more you can set up your error messages for all the supported languages and just call raiseerror with a number and magically you will get all error messages in that language.

Now, if that is not the case go to client side and use regional settings overthere as pointed out before

* Noel

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 6

Thanks a lot, guys.

Probably we will move this culture specific conversion to application. Developers just asked me to add extra field "Culture Name" and will use probably "CultureInfo" class in .NET.

Cheers