CODEWITHSUNDEEP
sql-serverinternationalizationdoubledecimal
Ernst V
Ernst V

Reputation: 31

SQL INSERT INTO: comma as decimal

My problem is that my customer runs his SQL Server on a Windows box and the country settings are set to "Germany".

This means, a decimal point is NOT a point ., it's a comma ,!

Inserting a double value to the database works like this

INSERT INTO myTable (myPrice) VALUES (16,5)

Works fine, so far.

The problem comes up if there is more than one value with decimal places in the statement like

INSERT INTO myTable (myPrice, myAmount) VALUES (16,5,10)

I get the error

Number of query values and destination fields are not the same.

Can I somehow "delimit" the values? Tried to add brackets around but this does not work.

Unfortunately I cannot change the language settings of the OS or the database because I am just writing some add-ons to an existing application.

Thank you! ev

Upvotes: 3

Views: 2459

Answers (1)

Gaurava Agarwal
Gaurava Agarwal

Reputation: 984

You must put the data in the format allowed by database. Even if you put data using comma... You may loose out numerical calculations.

If I get such situation.. I will check if comma is only required for visibility.. then I would display values in comma format while store them in decimal format.

This way data can be easily processed as numeric. But need to change it to and fro only for UI or display.

Based on this you may describe your situation in more detail if required.

EDIT: To verify my theory can you check if this insert statement has inserted 165 or 16.5 in the database.

  INSERT INTO myTable (myPrice) VALUES (16,5);
  select from mytable where myprice <17;

Upvotes: 2

Related Questions