Microsoft KB Archive/811036

= PRB: SQL Server Returns English Format for Numeric Data Regardless of the Collation Settings =

Article ID: 811036

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.



STATUS
This behavior is by design.



MORE INFORMATION
To have SQL Query Analyzer use the format settings from the regional settings of the client computer:
 * 1) Start SQL Query Analyzer.
 * 2) On the Tools menu, click Options.
 * 3) 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.

