?
Solved

Decimal delimiter in TSQL

Posted on 2010-01-03
4
Medium Priority
?
1,159 Views
Last Modified: 2012-05-08
Hello again,

another problem occurs when importing some data into a table. Steps I do:

1. Import CSV file into dataset (VB.NET)
2. Use dataset.getxml method to retrieve XML string.
3. Call stored procedure that selects the fields of XML parameter

The parameter is passed as XML, the content of the XML parameter is attached and the stored proc can be viewed in the code section. The problem is that the numbers are wrong formatted, TSQL sees the point as decimal delimiter. E.g. the value 2.946 is imported as 2,95. This shouldn't happen (image of regional settings attached). Of course I could replace the point, but the productive system is an english version, so that's no option for me.
Btw. all software products I use in development are german versions.

rs.GIF
getxml.txt
0
Comment
Question by:Stephan_Schrandt
  • 2
4 Comments
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 600 total points
ID: 26165332
English settings treat . as decimal separator so the result is normal. you should manage the point in your code somewhere  before importing to the sql server.
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1400 total points
ID: 26165829
Hallo Stephan,

There is no choice. MSSQL is considering a point always as a decimal delimiter. You need to remove thousands delimiters, and use a decimal point, as soon as you are handling numeric values as strings. That is, your XML stream has to be cleaned up.

Grüße aus Südbaden!
0
 
LVL 9

Author Comment

by:Stephan_Schrandt
ID: 26165852
Yes, but I have a german version of SQL Server and german regional settings (development environment).  Does SQL Server always treat point as decimal seperator ignoring regional settings?
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 26165873
No matter which locale (regional setting) you use, and whether it is a German or US English version of MSSQL, they behave the same. Only difference is for default codepage used for collations.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question