If I run the following SQL from Interactive-SQL on version 16.0.0.2798

begin
  declare test numeric(4,2);
  set test = 40.73;

  select test as testNumeric,
         string(test) as testString
     for json raw;
end

The result I get is:

forjson(256,'testNumeric',jsontab."1",'testString',jsontab."2")
[{"testNumeric":40.729999999999994,"testString":"40.73"}]

How can I get "testNumeric" to be represented as 40.73 in the output?

asked 10 Dec '19, 07:17

Christian%20Hamers's gravatar image

Christian Ha...
53191426
accept rate: 33%

1

FYI V17 does the same thing. The docs are [cough] thin on JSON, so perhaps STRING() is the [cough] only answer.

(10 Dec '19, 07:59) Breck Carter
Replies hidden
Comment Text Removed

The thing with STRING() is that you get additional double quotes...

(10 Dec '19, 09:08) Christian Ha...

Although this seems to be a common JSON issue, it seems that numbers are converted to double in several JSON libraries based on JavaScript...

(10 Dec '19, 09:09) Volker Barth

I add that the number in JSON is stored as a double- precision floating-point, and probably the standard IEEE-754 will explain your result better.

But I agree with you, something is wrong there, instead of 40.729999999999994 I expected to see 40.7299999999999968736119626556

So, it is better to follow Breck's advice :)

(10 Dec '19, 09:21) Vlad
Replies hidden

> you get additional double quotes

Does that matter? It doesn't in HTML, you can code WIDTH=200 or WIDTH="200" and the browsers don't seem to mind.

BUT... you are correct, and I didn't notice the "quotes".

Caveat Emptor: I am clueless about JSON :)

(10 Dec '19, 11:45) Breck Carter

Strictly speaking, that should be written as "Breck's [cough] advice" :)

(10 Dec '19, 11:52) Breck Carter

Still an advice though. I played a little bit with JS, and it is interesting. My browsers Ch&FF do not have issues with the precision. Maybe they are using some heuristics or whatever... and instead of number(4,2), I'd experiment with decimal (or high precision values), or... just use the string, yes, with quotes. It doesn't matter what precision you might have, but at some point of time you will get these ,99999999999999999999991 values.

(10 Dec '19, 12:05) Vlad

If you set the test variable to 0.73 I would suspect similar behaviour as described. But then you get

[{"testNumeric":0.73,"testString":".73"}]

I would suspect

[{"testNumeric":0.729999999999994,"testString":"0.73"}]

So for value < 1 we need to use it without string() and for values > 1 we need to use. However that can not be solved in an if -statement.

Any solution for that?

(11 Dec '19, 04:53) Frank

I don't think the issue is limited to values > 1 vs. <= 1 although using only 2 fractional digits seems to be more common when values are between [-1;1].

E.g. when adapting the expression by varying subtrahends, I get two digits with "test = 40.73 - 44" but not with "40.73 - 45".

begin
  declare test numeric(4,2);
  set test = 40.73 - 45;

  select test as testNumeric,
         string(test) as testString
     for json raw;
end
(11 Dec '19, 05:21) Volker Barth
showing 2 of 9 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19
×5

question asked: 10 Dec '19, 07:17

question was seen: 258 times

last updated: 11 Dec '19, 05:22