INF: Conversion of Floating Point Values to Character Strings

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

Several methods can be used to retrieve floating point data from SQL Server and convert the results to a character representation. The primary methods for performing such a conversion are: the Transact-SQL CONVERT() and STR() functions, the DB-Library dbconvert(), and C runtime library functions such as _fcvt(). The conversions performed by each method are described below.

MORE INFORMATION

Using the Transact-SQL CONVERT() function to convert the value of a FLOAT or REAL datatype into a character string on the server results in a character string containing a maximum of six decimal digits.



With OS/2 SQL Server K9, CONVERT() will convert FLOAT values to an arbitrary number of digits and will not convert FLOATS to an exponential form. However, any decimal digits past the first 16 will be displayed as zero. REAL values convert as noted above, with numbers greater than or equal to 1E+006 being represented in exponential notation.

If conversion to a specified number of decimal digits is desired, or if precise control of the number of places after the decimal is needed, the Transact-SQL STR() function should be used. This function allows the optional specification of both the length of the resultant character string, and the number of digits after the decimal place that will be displayed.

The string returned by the DB-Library dbconvert() function can vary between different versions of DB-Library. Under the 1.1x and 4.20.00 libraries, dbconvert() converts FLOAT datatypes to a string containing a maximum of 16 digits unless the string has 17 or 18 digits before the decimal place. In this case, 17 or 18 digits are displayed in the character string, although only 16 are significant. REAL datatypes are converted in an inconsistent manner, typically to 16 digit strings, of which only the first 6 digits are meaningful. Note that REAL was not a supported datatype under SQL Server 1.11.

With DB-Library 4.20.21, the dbconvert() function always displays 6 decimal digits after the decimal point, 0 (zero) padding to 6 digits if necessary. If less than 7 decimal digits appear before the decimal point, 4.20.21 dbconvert() will display all digits and zero pad after the decimal point as described previously. If 7 or more decimal digits appear before the decimal point, the number is displayed in exponential notation, zero padded if necessary, with 13 decimal digits.

With DB-Library 4.20.32, dbconvert() displays an arbitrary number of digits after the decimal point, up to a maximum of 16 decimal digits for the total character representation of the number. Although 16 digits may be displayed for a REAL, it should be remembered that only the first 6 are significant. If greater than 16 decimal digits would appear before the decimal, the number is displayed in exponential notation, with 16 digits for a FLOAT and 7 digits for a REAL datatype.

Conversion of a floating point value to an arbitrary number of character digits can be accomplished in two ways: the floating point value can be retrieved from SQL Server as DBFLT4 or DBFLT8 datatypes and converted by the application program using routines such as the C runtime functions _fcvt(), _ecvt(), printf(), and sprintf(), or the floating point value can be converted on the server using the STR()function, and the resultant character string returned to the front end.

The character representation does not reflect the actual precision of the floating point value, which will be either 15 or 6 decimal digits for FLOAT and REAL datatypes, respectively.

Additional query words: Transact-SQL float double conversion round truncate dblib data type

Keywords          : kbprg SSrvDB_Lib SSrvProg 
Version           : 4.2 | 4.2 | 4.2
Platform          : MS-DOS OS/2 WINDOWS 
Issue type        : 

Last Reviewed: March 16, 1999