Comma vs dot as decimal separator

Question

I have a C# app that writes some data into a SQLite database, the problem comes when I try to store a C# double into a SQLite REAL field.

It saves it into the database with a comma as decimal separator (as usual in my country, not sure it counts for anything).

The table has the following structure:

CREATE TABLE `valoresStock` (
  `Fecha`   TEXT,
  `IdArticulo`  TEXT,
  `IdColor` INTEGER,
  `KgPorUnidad` REAL,
  `Stock`   REAL,
  `CostePorKg`  REAL,
  `CostePorUnidad`  REAL,
  PRIMARY KEY(Fecha,IdArticulo,IdColor)
);

And the query I use in my app is the following:

cmd.CommandText = "INSERT INTO valoresStock (Fecha, IdArticulo, IdColor, KgPorUnidad, Stock, CostePorKg, costePorUnidad) VALUES ('" + DateTime.Today + "','" + referencia.idArticulo + "', '" + referencia.idColor + "', '" + referencia.kilos + "', '" + referencia.stockActual + "', '" + referencia.valorPorKg + "', '" + referencia.valorPorUnidad + "') ";

As I said, the four last fields are of type double. No error is given.

Then the data is stored as follows:

data structure

That is, using a dot when it doesn't have decimal part, and a comma when it does.

The problem with this is for example when I use a SELECT statement to calculate, such as this:

SELECT VS.Fecha, VS.IdArticulo, VS.IdColor, (VS.Stock * (VS.CostePorUnidad + VS.CostePorKg * Vs.KgPorUnidad)) AS Valor FROM valoresStock VS

This gives the following result, which is wrong (only took the number with the dot).

error with commas

So If I manually change the commas for dots in my table, it works properly.

all with dots

But how to make my app do this, some kind of culture change?

Or can I make SQLite understand or automatically transform the commas?

Why does SQLite accept that number as valid if it doesn't understand it later?

Yet another opportunity where you should be using parametrized queries. — O. R. Mapper, Jan 22 '15 at 08:37

score 6 · Accepted Answer · answered Jan 22 '15 at 08:49

You should be using a parametrized query for inserting values. That means that you should only use parameter placeholders in your SQL string and add the actual values as query parameters.

This is generally a good practice to prevent SQL injection attacks. While those may be a lesser issue here, if all of your values are numerical (and possibly do not even come directly from user input), this parametrization also serves for ensuring that parameter values get formatted just like the target engine wants/needs them. Floating point numbers will be recognized without you having to worry about the correct decimal separator, and strings will be recognized without you having to worry about the correct quotation marks and escaping.

So, your INSERT statement should be changed as follows:

cmd.CommandText = "INSERT INTO valoresStock (Fecha, IdArticulo, IdColor, KgPorUnidad, Stock, CostePorKg, costePorUnidad)"
    + "VALUES (@fecha, @idArticulo, @idColor, @kgPorUnidad, @stock, @costePorKg, @costePorUnidad)";

Then, you can add your parameter values to the command like this:

cmd.Parameters.AddWithValue("@fecha", DateTime.Today.ToString(System.Globalization.CultureInfo.InvariantCulture));
cmd.Parameters.AddWithValue("@idArticulo", referencia.idArticulo);
cmd.Parameters.AddWithValue("@idColor", referencia.idColor);
cmd.Parameters.AddWithValue("@kgPorUnidad", referencia.kilos);
cmd.Parameters.AddWithValue("@stock", referencia.stockActual);
cmd.Parameters.AddWithValue("@costePorKg", referencia.valorPorKg);
cmd.Parameters.AddWithValue("@costePorUnidad", referencia.valorPorUnidad);

All the floating point values will then be automatically recognized for what they are and stored appropriately.

One thing to note is that I have explicitly converted DateTime.Today to a string for two reasons:

DateTime is not a primitive type, so I'm not entirely certain what the database engine would do to it.
You have declared the column Fecha as having type TEXT, so I presume you want a textual representation of the date in your database rather than anything which could be "evaluated".

In order to ensure that your program writes the same data, no matter on what machine it is executed, I have used the InvariantCulture CultureInfo instance, which provides, so-to-speak, a "neutral" format independent of the current machine's culture settings. If you also have the program expect this format in case you ever parse the date, the database files written by your application will always be compatible, even when the application is used across machines with different culture settings.

I declared Fecha as TEXT because SQLite hasn't a datatype for dates, as described here: https://www.sqlite.org/datatype3.html So I followed its recommendation to use TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). — Pinx0, Jan 22 '15 at 09:19
@Pinx0: Oh rigt; in that case, rather than `ToString(System.Globalization.CultureInfo.InvariantCulture)`, you could convert the `DateTime` into one of the supported formats by e.g. explicitly specifying the appropriate format string. [This SO question](http://stackoverflow.com/questions/249760/how-to-convert-unix-timestamp-to-datetime-and-vice-versa) provides another method. — O. R. Mapper, Jan 22 '15 at 09:23

score 0 · Answer 2 · answered Jan 22 '15 at 08:52

I would solve the issue in the .NET side rather than in the database side.

In .NET, number formatting depends on the culture. For example:

// This prints 2.3 - Neutral, invariant culture (English notation)
string withDot = (2.3d).ToString(CultureInfo.InvariantCulture.NumberFormat);

// This prints 2,3 - Spanish culture
string withComma = (2.3d).ToString(new CultureInfo("es-ES"));

score -1 · Answer 3 · answered Jan 22 '15 at 08:41

-1

I can be wrong, but maybe DefaultThreadCurrentCulture property can help you.

answered Jan 22 '15 at 08:41

Atchitutchuk

174
5

Comma vs dot as decimal separator

3 Answers3

Linked