Decimal delimiter in TSQL

Posted on 2010-01-03
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.

Question by:Stephan_Schrandt
    LVL 26

    Assisted Solution

    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.
    LVL 67

    Accepted Solution

    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!
    LVL 9

    Author Comment

    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?
    LVL 67

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now