Some datatypes doesn't honor localization

Fri Jan 14, 2011 by Peter Larsson in sql-server-2008, administration, sql-server-2005

This bug has haunted me for a while, until today when I decided to not accept it anymore.
So I filed a bug over at connect.microsoft.com,
https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed.

Here is a very simple repro of the problem

DECLARE @Sample TABLE
         (
             a DECIMAL(38, 19),
             b FLOAT
         )

INSERT   @Sample
         (
             a,
             b
         )
VALUES   (1E / 7E, 1E / 7E)

SELECT   *
FROM     @Sample

Here is the actual output.

a b

--------------------------------------- ----------------------

0.1428571428571428400 0,142857142857143

I think that both columns should have the same decimal separator, don't you?

//Peter

Legacy Comments


dd 2011-01-17	re: Some datatypes doesn't honor localization Usually I am all for diversity. But when it comes to interfaces, I oppose diversity. The more interfaces we have the more confusing it gets. We should work at standardizing interfaces, so that there is less need for metadata. Decimal separator is one of the interfaces. Its time the world standardized on the dot . as the decimal separator (simply because it the majority way). Duh.

Peso 2011-01-17	re: Some datatypes doesn't honor localization I don't care if it's a dot or comma, what I do care for is that both columns should have the same decimal separator.

Chris Sherlock 2011-01-17	re: Some datatypes doesn't honor localization dd, I agree. Let's get rid of localization entirely. In fact, there should be no other language for SQL Server than English, and everything should follow U.S. standards entirely. All dates should be mm/dd/yyyy, all units in inches and feet and definitely no Celsius for temperature measurements!

Nigel 2011-01-17	re: Some datatypes doesn't honor localization Please don't get me started on the US using the wrong Date format, the wrong paper sizes the wrong units for weights nd lengths etc. Aaaagghhhh!

Fabiano Neves Amorim 2011-01-17	re: Some datatypes doesn't honor localization Hi Peso, It's a datatype and a SSMS related problem, if you run it at SQL Server Query Analyzer (SQL 2000 :-(), you will see that the results are: a b ---------------------------------- ----------------------------------------------------- .1428571428571428400 0.14285714285714285 They use the same decimal separator "." There is another problemn with rounding: SSMS SELECT CONVERT(Float, 2.55), CONVERT(Float, 2.54) Results: ---------------------- ---------------------- 2,55 2,54 Query Analyzer SELECT CONVERT(Float, 2.55), CONVERT(Float, 2.54) Results: ----------------------------------------------------- ----------------------------------------------------- 2.5499999999999998 2.54

David 2011-01-17	re: Some datatypes doesn't honor localization No, the world standard is not "."; it's the "," look for the ISO standard... now the ISO standard says that "." can be used too... Please, don't think that US is the center of the world!

Tara 2011-01-17	re: Some datatypes doesn't honor localization What? The US isn't the center of the world? Hmmm, that's not what I've been taught, lol. Dot or comma, doesn't matter to me, just pick one and stick with it!

Peso 2011-01-17	re: Some datatypes doesn't honor localization The output should match the value of SET LANGUAGE, both columns. Period (pun intended).

Jon 2011-01-20	re: Some datatypes doesn't honor localization I've never noticed that but I fully agree peso. Ill vote it!

John @ SQL Hosting 2011-02-03	re: Some datatypes doesn't honor localization Of course they should have the same separator, but the don't and I don't think Microsoft will do anything about it anytime soon.

subash 2011-02-17	re: Some datatypes doesn't honor localization Hi, I get the "." as decimal separator in both cases. DECLARE @Sample TABLE( a DECIMAL(38, 19), b float ) INSERT @Sample(a,b) VALUES(1E / 7E, 1E / 7E) SELECT * FROM @Sample Result: a = 0.1428571428571428400 b = 0.142857142857143

BugConfirm 2011-06-10	re: Some datatypes doesn't honor localization Yes.. i can confirm this issue with the localized separator. Its really annoying when you try to copy a result table into another app like excel and your numbers will be converted to such stupid values like '19.Jun' because of a numeric value '19.6' . and also yes.. it is not the same using a float type instead of numeric or decimal , because float will partly automatically convert to an 'E'-format which is not always the desired output. Additinally i agree in the demand of "Its time the world should standardize." It would be a good idea Microsoft will use ISO formats in their basic programming of all(!) OS and Apps and treat their own localization in the same way they treat all foreign countries localization, namely as an additional translation module.