PRB: SQL Server Returns English Format for Numeric Data Regardless of the Collation Settings (811036)

SYMPTOMS

SQL Server returns data in English format when you retrieve money data type or real data type from a table. The table can have any collation settings.

CAUSE

SQL Server stores the numeric data only in one format regardless of the collation setting or language version. With money, real, or float data types, data is stored in binary format based on the specified precision.

WORKAROUND

The client application should format data according to the settings of the client computer locale.

In SQL Query Analyzer, use the following example to format money data type in French format:

Use Northwind 

GO

SELECT unitprice EnglishFormat, replace (replace (cast(unitprice as varchar), ',', ''), '.', ',') FrenchFormat 
FROM  [Order Details]

The output of formatted data is in French format.

MORE INFORMATION

To have SQL Query Analyzer use the format settings from the regional settings of the client computer:

Start SQL Query Analyzer.
On the Tools menu, click Options.
On the Connections tab, click to select Use regional settings when displaying currency, number, dates, and times.

If you change this attribute, the Microsoft OLE DB Provider for ODBC gives priority to the client locale setting when it converts date, time, and currency values to character strings at the statement level.

REFERENCES

For additional information, visit the following Microsoft Web site and see the "SQL_SOPT_SS_REGIONALIZE" topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_s_8pv6.asp

For more information about how to write international Transact-SQL statements, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: