May 21, 2009 at 7:29 pm
Hi,
SQL understands currency.
When you are using the MONEY type, the SQL engine accepts an action $100 + 20 and returns "120".
I expected to find the "$" somewhere inside dbo.master or buried in a locale setting that I can access with a simply query. This will allow currency symbol to be added to the return i.o.w currencySymbol + CAST(ans AS VARCHAR) = $120
How do I find out what SQL will accept as the currency i.e "$" ?
Thanks.
May 21, 2009 at 8:09 pm
By default SQL Server picks this up from Windows from the server's language/locale settings.
The setting within SQL Server can be changed to something different at the SQL Server level by using sp_configure to change the default language. It can also be set at the connection level.
May 22, 2009 at 7:54 pm
I performed:
EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE
GO
EXEC sp_configure
Even after this I'm still no closer to finding out what the SQL engine will accept as the currency symbol. Somewhere the SQL engines knows that a "$" can be part of a MONEY value.
I need to know this because I need to display the currency symbol as part of a script return value that is deployed in the UK (pound symbol), Spain (Euro symbol) and Japan (Yen symbol).
Is there a SQL script that pull this information from the locale?
May 24, 2009 at 8:44 pm
The ASCII code for the dollar sign is decimal 36. I've got no idea whether that same code is used for the other currency symbols when the connection is using a different language. Try executing the following to see what it gives you on the various systems:
select char(36)
May 24, 2009 at 9:16 pm
This BOL article has the list of all of the currency symbols that SLQ Server allows.
I say "allows" instead of "recognizes" because apparently it does not do anything with the currency symbol, it just doesn't throw a datatype conversion error if it's there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2009 at 12:43 pm
Why not just adding a CurrencyTable which contains all currency names and their signs?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply