|
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? |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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:
question asked: 10 Dec '19, 07:17
question was seen: 688 times
last updated: 11 Dec '19, 05:22
FYI V17 does the same thing. The docs are [cough] thin on JSON, so perhaps STRING() is the [cough] only answer.
The thing with STRING() is that you get additional double quotes...
Although this seems to be a common JSON issue, it seems that numbers are converted to double in several JSON libraries based on JavaScript...
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 :)
> 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 :)
Strictly speaking, that should be written as "Breck's [cough] advice" :)
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.
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?
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