CODEWITHSUNDEEP
sql-serverlocalizationwindows-server-2008sqldatatypes
TechyGypo
TechyGypo

Reputation: 834

Not Storing Decimal Point in SQL Server 2008 R2 Express

I am in the UK. I have a Windows 2008 server in Germany with SQL Server 2008 R2 Express installed on it. The regional settings for Windows are set to UK. SQL Server language is set to English. When I run

sp_helplanguage @@LANGUAGE

it shows that it is set to

us_english

I have numerous tables in the database that have float datatypes in them. When I use SSMS to change one of the float values, if I type in

1234.1234

firstly it displays as

1234,1234

then when I click off the row to save it, it displays as

12341234

The ASP.NET application, that is being served the data via a stored procedure and being put into a double (VB.NET), does a

String.Format("{0:#0.0000}", dMyDouble)

This renders as

12341234,0000

Needless to say, on my local server (all UK based) all database entered numbers display as I would expect (1234.1234) and .NET formats them as I would expect (1234.1234). I am aware that my European friends use a different notation to us in the UK, but I need the UK format to be output - and more importantly the float in the database!

The fact that .NET is not formatting correctly, I imagine is purely to do with the fact that the number is not storing the accuracy.

I have also played around a bit by using a decimal column (18, 4) and I get a similar outcome. As I type 1234.1234 using the point (.) it replaces with a comma (,). When the row is saved, it saves as

12341234.0000

Upvotes: 1

Views: 1606

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294317

You must set the LANGUAGE on your session to the desired setting.

But, despite the fact that you did not show any C# code, the fact that you're seeing formatting issues it means you're passing the values as text in SQL Commands. You should be using @variables instead. This would have the side benefit of avoiding SQL injection issues.

Upvotes: 2

Related Questions