September 7, 2006 at 1:51 pm
I had a couple general questions concerning international data.
1. What is the best way to deal with Dates in the 'DD/MM/YYYY' format? Does SQL Server regardless of Collation settings and language settings, only accept dates in our (USA) standard format of 'MM/DD/YYYY'? So does the rest of the world have to change their dates when for example importing data in to SQL Server to match SQL Server's internal storage of dates? You could store the data as a varchar, etc. but perfer to store as a regular date field.
2. What is the best way to handle numeric values from a country that uses decimals instead of commas and where we use a decimal they use a comma? The more general question is if SQL Server is installed with that countries regional settings and Collation, would they still have to change their format to the correct format for Float or numeric data that matches the USA standard?
If someone is familiar with these type of issues, I would be greatful to hear your opions.
Thanks,
Daniel
September 8, 2006 at 12:57 am
I program SQL Server 2005 for applications in Europe with visual basic 6 and ADO. Our date format is dd/mm/yyyy and number format is 123.123,99. Never had any problems. We have our server collation to the appropriate language and all the date/time fields set to datetime or smalldatetime.
Our queries (all of them dynamic sql strings built at the client) simply format the dates to yyyymmdd and everything works fine.
Our numeric fields are either currency or decimal and I have not seen any problems there also.
You should try reading SQL Server Books Online. They contain an excellent chapter on how to use the international features of SQL Server. You can CAST and CONVERT almost anything into anything. I don't see we you might worry about these things.
September 8, 2006 at 7:16 pm
Dates are stored in a numeric form that doesn't look anything like dd/mm/yyyy or mm/dd/yyyy or any variation thereof (if they are datetime or smalldatetime). The numeric values in the database are the same all over the world. You only have to worry about how those numeric values are formatted when entering or displaying them.
Usually you can count on the localization of the server where the application runs to automatically interpret text dates, numbers, and currency using the local standards when inputting data. Sometimes outputs have the correct local format, sometimes it is up to your application to fix the format. You can avoid some localization problems by using a canonical format that can't be misinterpreted, such as YYYY-MM-DD.
If you are getting date values from other countries, a bigger question is how you are handling time zones. You can enter '9/8/2006 12:57AM' in New York and '8/9/2006 12:57AM' in London and '8.9.2006 12:57AM' in Berlin and get identical values, but they're really hours apart. And subject to Daylight Savings Time variations.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply