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 18.104.22.1685 and SA 10.0.1
Thanks for your help. Thomas
asked 05 Feb '10, 11:49
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
select str( -1234567890123456789012345.0001,40,2) from sys.dummy
If you want to convert a numeric to a string, use CAST.
answered 05 Feb '10, 13:19
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