Is '-12345678901234566.00' the correct result of str(-12345678901234567.0001,40,2)

[thomas_duemesnil]

When i execute the following command I'm a little bit surprised of the result.

select -12345678901234.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567890.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678901.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789012.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -12345678901234567890123.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -123456789012345678901234.0001 as TestVal, str( TestVal, 40, 2) as stringVal
union all 
select -1234567890123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal
order by TestVal desc

The result is in iSQL

TestVal                         stringVal                                
------------------------------- -----------------------------------------
-12345678901234.0001                                  -12345678901234.00 
-123456789012345.0001                                -123456789012345.00 
-1234567890123456.0001                              -1234567890123456.00 
-12345678901234567.0001                            -12345678901234566.00 
-123456789012345678.0001                          -123456789012345680.00 
-1234567890123456789.0001                        -1234567890123456800.00 
-12345678901234567890.0001                      -12345678901234567000.00 
-123456789012345678901.0001                    -123456789012345680000.00 
-1234567890123456789012.0001                  -1234567890123456800000.00 
-12345678901234567890123.0001                -12345678901234568000000.00 
-123456789012345678901234.0001              -123456789012345690000000.00 
-1234567890123456789012345.0001            -1234567890123456800000000.00 
(12 rows)Execution time: 0.016 seconds

The Function STR is described as

Syntax 
STR( numeric-expression [, length [, decimal ] ] )

Parameters 
numeric-expression    Any approximate numeric (float, real, or double precision) expression between -1E126 and 1E127.
length    The number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.
decimal    The number of decimal digits to be returned. The default is 0.

I think I did not overcome a length described as -1E127 I have tested this with SA 11.0.1.2355 and SA 10.0.1

Thanks for your help. Thomas

[Former Member · Accepted solution]

To clarify Mark's answer - STR() takes as its argument an approximate numeric argument (real or double), and not a numeric value of any type.

In your example the argument to STR() is numeric. The server (properly) parses the query and assigns the type to match the input, which in the case of the first SELECT is numeric(18,4). On evaluating STR(), the server is converting the numeric(18,4) value to float before converting it to a string, with the subsequent loss of precision.

select cast( -1234567890123456789012345.0001 as real) from sys.dummy

gives

-1.2345679468E24

and

select str( -1234567890123456789012345.0001,40,2) from sys.dummy

gives

-1234567890123456800000000.00

If you want to convert a numeric to a string, use CAST.

[thomas_duemesnil · Accepted solution]

Fully accepted. But the Documentation states 1E127. This is a much larger number isn't it ? I would expect to read numeric(18,4) in the documentation.

[Former Member · Accepted solution]

The first select uses numeric(18,4) because that is the precision and scale of the constant. The last SELECT in your UNION query is built as a numeric(29,4).

select exprtype( 'select -1234567890123456789012345.0001 as TestVal, str( TestVal, 40, 2) as stringVal from sys.dummy', 1)

gives

numeric(29,4)

The documentation states the maximum exponent that can be supported, but not the size of the mantissa. The double type is limited to 63 binary digits of precision (approximately 16 decimal digits) and that's it.

Again - if you want to convert numeric to string, use CAST.

[MarkCulp · Accepted solution]

What you are seeing is the result of the evaluation of the string value as a double. The precision of a double is about 16 decimal digits - see http://en.wikipedia.org/wiki/Double_precision_floating-point_format - and as a result the 17th digit in your conversion of the double back to a string (i.e. STR() operator) will show you varying digits in the 17th decimal position depending on the binary respresentation of the number.

The 11.0.1 documentation talks about this here.