To provide some background, I am currently working on a project of transitioning an Access database and its code to SQL.
In the process I changed Access data types of Double to Float in SQL Server; I did this because these data types are closely related and because my database performs a lot of division and multiplication (something I heard floats were best for).
Another issue of converting the database arose in the fact that Access uses bankers rounding whereas SQL does not; I went out and found two UD bankers rounding functions, both are not yielding consistent bankers rounding results as they should.
Is this inconsistency something I should expect when trying to run these bankers rounding functions (which include subtraction and addition) on float numbers?
The following are the two functions...
FUNCTION [dbo].[RB](@Val FLOAT, @Digits INT)
RETURNS FLOAT
AS
BEGIN
RETURN CASE WHEN ABS(@Val - ROUND(@Val, @Digits, 1)) * POWER(10, @Digits+1) = 5
THEN ROUND(@Val, @Digits, CASE WHEN CONVERT(INT, ROUND(ABS(@Val) *
POWER(10,@Digits), 0, 1)) % 2 = 1 THEN 0 ELSE 1 END)
ELSE ROUND(@Val, @Digits)
END
END
FUNCTION [dbo].[RoundBanker]
( @Amt NUMERIC(38,16)
, @RoundToDecimal TINYINT
)
RETURNS NUMERIC(38,16)
AS
BEGIN
DECLARE @RoundedAmt NUMERIC(38,16)
, @WholeAmt INT
, @Decimal TINYINT
, @Ten NUMERIC(38,16)
SET @Ten = 10.0
SET @WholeAmt = ROUND(@Amt,0, 1 )
SET @RoundedAmt = @Amt - @WholeAmt
SET @Decimal = 16
WHILE @Decimal > @RoundToDecimal
BEGIN
SET @Decimal = @Decimal - 1
IF 5 = ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal + 1 ) ,0,1) -
(ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) * 10) )
AND 0 = cast( ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) -
(ROUND(@RoundedAmt * POWER( @Ten, @Decimal - 1 ) ,0,1) * 10) )
AS INTEGER ) % 2
SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 1 )
ELSE
SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 0 )
END
RETURN ( @RoundedAmt + @WholeAmt )
END
SQL Server ROUND() function follows IEEE Standard 754 and uses the "round up" algorithm when you use float type. Use decimal if you need more precision. dont use use float or real.
refer this link http://msdn.microsoft.com/en-us/library/ms187912.aspx