I have a SQL Server 2005 database set to locale Turkish_CI_AS. The locale setting of the pc of the SQL Server is set to Turkish. Turkish decimal separator is "," and thousands separator is ".". There is a field with datatype decimal(14, 3) and it has a record with value "400,123" (that's four hundred). When I use Open Table in SQL Manager the value is correct in the result grid. But when I query the table in SQL query window the result value is "400.123" which is wrong. I also get this wrong result in my Linq query results. What should I do to get the correct value?
A:
Try altering the table column to specife collation_name like Alter column myNogoodColation Collate Turkish_BIN
look here for help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/56483d24-add7-483d-9b96-c6fda460ddbc.htm
COLLATE { | database_default } :: = { Windows_collation_name } | { SQL_collation_name }
TonyP
2010-02-06 09:19:02
It seems you can't use BIN collation with data type decimal: Expression type decimal is invalid for COLLATE clause.
Armagan
2010-02-06 14:27:12
A:
What is default language of the login that you are using? You can check it by selecting sys.server_principals, or ask DBA if you don't have rights to see the login in that view http://msdn.microsoft.com/en-us/library/ms188786.aspx
Piotr Rodak
2010-02-07 02:24:55
I checked it, it is set as "us_english". Also SELECT @@language says "us_english" (are these related?).
Armagan
2010-02-08 12:05:38
A:
The only way I ever got out of this spot was to convert the decimal to a string, replace the dot with nothing (aka empty string) and then the comma with a dot. As in
Replace(Replace(Cast(<value> as varchar(14)), '.', ''), ',', '.')
Not elegant but helps to convert from Excel to SQL server where I encountered the problem so far.