April 23, 2025 at 8:26 am
Quick one I hope in case I'm heading off in entirely the wrong direction!
I've been working on a product for some time now that had an entirely UK-only customer base. The powers that be now want to flog it to Australia and the ROI. For one reason or another, there are a few places in the database where we format numbers as currency. (Doing all of that in the presentation layer is not an option, at least in the short-term.) This code now has to support writing $1,234.56 or €1,234.56 instead of £1,234.56 based on some setting.
We had hoped to have a semi code-free approach by using FORMAT (1234.56, 'c') and then setting the default language of the appropriate entity (login, connection, etc.) but were slightly disappointed to find the list of available languages was surprisingly brief - sp_helplanguage lists neither Oz nor the ROI.
It seems we need to use FORMAT (1234.56, 'c', 'en-AU') etc., which means getting 'en-GB', 'en-AU' or 'en-IE' in to the code at runtime.
BOL for FORMAT specifically says for this third parameter; "it isn't limited to the languages explicitly supported by SQL Server."
We can't be the first people to split this particular atom so I was wondering if any wise old hands had any advice.
Thanks in anticipation.
April 23, 2025 at 8:33 am
Maybe have a setting somewhere which indicates whether to use UK, Australia or ROI.
Create a UDF to do the formatting which uses the environment setting. Change your code to use the UDF.
Using FORMAT is best avoided if possible, because of its horrendous performance issues.
April 23, 2025 at 9:06 am
Thanks. Yes, that is the plan of course. I was just wondering if it was even necessary - could we rely on some setting external to the database to make the decision for us?
April 23, 2025 at 9:45 am
Thanks. Yes, that is the plan of course. I was just wondering if it was even necessary - could we rely on some setting external to the database to make the decision for us?
You mean without changing any of your existing code? I don't think so.
You would be able to get the server's (not SQL Server's, but the hosting server's) currency code by reading the registry, but that doesn't seem like the best solution to me.
April 23, 2025 at 9:48 am
What type of client is in use? Is it some fat client or is it some sort of web based SaaS app you have.
Done something with SSRS in the past and language translation, where it pulls the language code from the browser and passes that in as a param to a proc to pull the right translations for the language the user has their browser set in.
Obviously not going to help if the the user is in Aus but have their browser language set to en-GB but maybe its something you mandate in your usage policies, don't go tampering with the browser language settings otherwise it'll screw things.
Might be best though having a "user preferences" table or a "company" table somewhere with settings for that user / company, and then they can pull the language code for an entity and format that way though.
April 23, 2025 at 3:09 pm
Quick one I hope in case I'm heading off in entirely the wrong direction!
I've been working on a product for some time now that had an entirely UK-only customer base. The powers that be now want to flog it to Australia and the ROI. For one reason or another, there are a few places in the database where we format numbers as currency. (Doing all of that in the presentation layer is not an option, at least in the short-term.) This code now has to support writing $1,234.56 or €1,234.56 instead of £1,234.56 based on some setting.
We had hoped to have a semi code-free approach by using FORMAT (1234.56, 'c') and then setting the default language of the appropriate entity (login, connection, etc.) but were slightly disappointed to find the list of available languages was surprisingly brief - sp_helplanguage lists neither Oz nor the ROI.
It seems we need to use FORMAT (1234.56, 'c', 'en-AU') etc., which means getting 'en-GB', 'en-AU' or 'en-IE' in to the code at runtime.
BOL for FORMAT specifically says for this third parameter; "it isn't limited to the languages explicitly supported by SQL Server."
We can't be the first people to split this particular atom so I was wondering if any wise old hands had any advice.
Thanks in anticipation.
Hey, I totally get what you're aiming for! If you're running into issues with formatting across different locales like AU, GB, or IE, one option might be to dynamically set the locale at runtime using the proper culture info. It could be useful to store the user’s region preferences and pass them to your formatting function. Also, consider checking the collation settings in SQL Server to see if they align with your region-specific formatting needs. Hope that helps!"
Let me know if you'd like to adjust anything!
April 23, 2025 at 3:15 pm
This was removed by the editor as SPAM
May 5, 2025 at 6:40 am
Personally I think any "solution" based on reading details from the client computer is a good way to have problems. You are relying on the client to have the "right" settings.
If you sell your product to an AUS company and they have an office in Singapore, do they want an AUD 100 value showing as AUD 100 or as SGD 100. Likewise for an IRL customer with an office in Belfast having an EUR 100 value showing as EUR 100 or GBP 100. In both cases I suspect the former is wanted.
My preference is to have a Settings table (you probably already have one) and add preferred currency code to that table. This will ensure the correct currency is shown regardless of how a client computer is configured.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 5, 2025 at 7:50 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply