PRB: STR() Inconsistent with Exact Numeric Data (154000)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
This article was previously published under Q154000

SYMPTOMS

The first parameter of the STR() function is float. Numeric data passed as the first parameter to the STR() function is first converted to float and then rounded.

STATUS

This is by design.

MORE INFORMATION

The following example demonstrates this scenario: 
declare @x1 numeric(8,6), @x2 numeric(8,6)
select @x1 = 12.104375, @x2 = 12.128175

select str(@x1, 9, 5) STR, round(@x1, 5) ROUND
select str(@x2, 9, 5) STR, round(@x2, 5) ROUND
The @x1 numeric value 12.104375 is first converted to float 12.104374999999999. The STR() function then rounds the fractional part of the number to the decimal you specified. In this example, STR() rounds 12.104374999999999 to 12.10437 (5 decimal places).

The @x2 numeric value 12.128175 is first converted to float 12.128175000000001. Then, STR() rounds 12.128175000000001 to 12.12818 (5 decimal places).

See SQL Server Books Online topic "STR".
Modification Type:MinorLast Reviewed:3/16/2005
Keywords:kbBug kbprb KB154000
©2004 Microsoft Corporation. All rights reserved.