Inserting numbers with commas

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  September 14, 2006 at 7:17 pm

  #115725

  I have created a German database (German collation)and want to insert some native German numbers and to display them in native German local.

  SQl server 2000 SP 4 on Win2003 server.

  Although I had the database collation set to German, I still needed to change the Language from Us to German in the control panel->Language. Then I needed to reconnect to the server and the numbers were displayed in their native German locale - 123,45 for 123 dollars and 45 cents (the decimal point is a comma)versus the inserted value of 123.45 (period is used for the decimal point). That was cool but then I tried to insert the value of 123,45 into the same decimal field and got an error. I thought that if I could display them correctly, I would be able to insert the numbers the same way.

  How I'm suppose to insert native numbers with comma for the decimal point and thousanths separator into a numeric field?

  Thanks a lot for the help.

  mj

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  September 15, 2006 at 2:30 pm

  #660827

  SET LANGUAGE statement wil allow you to enter dates with the day first then  month if the language is German. I don't think SQL Server supports input numbers with commas instead of decimal point because comma is also the field separator. Like in INSERT statement you say:

  INSERT(field1,field2,field3)

  Values (value1,value2,value3)

  if you would insert numbers with commas they will be confused with field separator. I would use a  function in your code to replace commas with dots for the input.

  For the output the Report Builder in Reporting Services does use comma or decimal point depending on the locale settings of the Data Source:

  http://msdn2.microsoft.com/en-us/library/aa337294.aspx

  Formatting Numerical Data [Report Builder] 

  Regards,Yelena Varsha