When i execute the following command I'm a little bit surprised of the result.
The result is in iSQL
The Function STR is described as
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 asked 05 Feb '10, 11:49 Thomas Dueme... |
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. answered 05 Feb '10, 13:19 Glenn Paulley 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. 3
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. |
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. answered 05 Feb '10, 13:10 Mark Culp |