Skip to content

How to deal with decimal separator differences between regions when importing decimal/float data from SQL LogScout/PSSDIAG #395

@PiJoCoder

Description

@PiJoCoder

In the US, dot is used as a decimal separator while in most European countries, and most of world, comma is used as such separator. From CoPilot:

"Yes, the use of decimal separators varies between the United States and many European countries.

The United States and other English-speaking countries like Canada, Australia, and New Zealand primarily use the dot (.) as the decimal separator
Most European countries, including Spain, France, Norway, the Czech Republic, and Denmark, use the comma (,) as the decimal separator

This difference in decimal separators can sometimes lead to confusion, especially in international contexts. For example, the number "1,234" in the US would be interpreted as one thousand two hundred thirty-four, while in many European countries, it would be interpreted as one point two three four (1.234)."

With this in mind, are we potentially facing the possibility that depending on which country the customer's SQL Server ran and which country the use is in, we may get unexpected results or error in some select reports?

Here's an example to illustrate on a US-based SQL Server

SELECT CONVERT(DECIMAL(12,1),'100000.4') US_decimal_separator
go
SELECT CONVERT(DECIMAL(12,1),'100000,4') Europe_decimal_separator
go
SELECT CONVERT(DECIMAL(12,1), REPLACE('100000.4', ',', '.')) AS DontCare_decimal_separator;
SELECT CONVERT(DECIMAL(12,1), REPLACE('100000,4', ',', '.')) AS DontCare_decimal_separator;

Results

100000.4

Europe_decimal_separator
---------------------------------------
Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

DontCare_decimal_separator
---------------------------------------
100000.4

DontCare_decimal_separator
---------------------------------------
100000.4

And then:

DECLARE @number DECIMAL(12,1) = 100000.4;
SELECT FORMAT(@number, 'N', 'de-DE') AS Europe_decimal_separator_and_digit_grouping;
SELECT FORMAT(@number, 'N', 'en-us') AS US_decimal_separator_and_digit_grouping;

Output shows the decimal and digit separators are the exact opposite:

Europe_decimal_separator_and_digit_grouping
----------------------------------------------------
100.000,40

US_decimal_separator_and_digit_grouping
----------------------------------------------------
100,000.40

This may cause confusion and misunderstandings. We can think of a "rule" between LogScout & SQLNexus to talk, for example "decimal separator is comma " or "decimal separator is dot" and import successfully. But in the report interfaces we need to accomodate locale somehow.

Sub-issues

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions