I have a question about this fiddle:
DECLARE @TestVal AS float = 8.88;
SELECT flt = @TestVal
, xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE)
, fmt17 = FORMAT(@TestVal, 'G17')
, fmt = FORMAT(@TestVal, 'G')
, cst = CAST(@TestVal AS nvarchar(50))
, fmt17_roundtrip = CAST(FORMAT(@TestVal, 'G17') AS float)
, fmt_roundtrip = CAST(FORMAT(@TestVal, 'G') AS float)
, cst_roundtrip = CAST(CAST(@TestVal AS nvarchar(50)) AS float)
;
https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=0cf05f882eb24f53e9484f043af99446
I was having trouble with some XML going out by default in scientific notation which, while not incorrect or inaccurate, isn't terribly readable.
I was originally using FORMAT(floatcol, 'G17') because of the comments on this documentation page that:
Note that, when used with a Double value, the "G17" format specifier ensures that the original Double value successfully round-trips. This is because Double is an IEEE 754-2008-compliant double-precision (binary64) floating point number that gives up to 17 significant digits of precision. We recommend its use instead of the "R" format specifier, since in some cases "R" fails to successfully round-trip double-precision floating point values. The following example illustrates one such case.
Well, today I found that it seems to add some extra insignificant digits to the string. In this particular example, they all "round trip" fine, but the G17 format has an extra insignificant digit.
And despite the fact that it might not affect me on a round-trip, I really don't want to be sending this to another party with extra digits.
Right now I am leaning towards changing to FORMAT('G'), but am not sure of the implications of that. Currently the format string used in these XML exports is a configuration setting stored in my system, so it's easiest to continue to use FORMAT since it requires no code changes.
So before I go testing on a variety of other values, my question is what is the difference between G and G17 overall and what problems might I run into using G instead of G17?
(Yes, this value needs to be a float, not a decimal or integer or currency, and the domain of the float can vary by different measurements/contexts).
System does need to run on 2012 and up.