PRB: STR() Inconsistent with Exact Numeric Data

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".