Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Some datatypes doesn't honor localization

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

Print | posted on Friday, January 14, 2011 9:09 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Feedback

Gravatar

# 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.
1/17/2011 8:07 AM | dd
Gravatar

# 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.
1/17/2011 9:42 AM | Peso
Gravatar

# 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!
1/17/2011 9:54 AM | Chris Sherlock
Gravatar

# 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!
1/17/2011 10:19 AM | Nigel
Gravatar

# 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
1/17/2011 11:24 AM | Fabiano Neves Amorim
Gravatar

# 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!

1/17/2011 11:41 AM | David
Gravatar

# 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!
1/17/2011 6:47 PM | Tara
Gravatar

# re: Some datatypes doesn't honor localization

The output should match the value of SET LANGUAGE, both columns.
Period (pun intended).
1/17/2011 6:56 PM | Peso
Gravatar

# re: Some datatypes doesn't honor localization

I've never noticed that but I fully agree peso.
Ill vote it!
1/20/2011 9:04 PM | Jon
Gravatar

# 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.
2/3/2011 10:14 PM | John @ SQL Hosting
Gravatar

# 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
2/17/2011 12:53 AM | subash
Gravatar

# 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.
6/10/2011 10:08 AM | BugConfirm
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET