Hi,
I need to round to the second decimal place (AwayFromZero method):
Convert 2.565 to 2.57

Attempts that did not work:
ROUND(number,2)
CAST(number as Decimal(18,2))

Looks like I could use STR(number, 33, 2) and then CAST it back to Decimal, but I'm wondering if there is a better way. Also, does anyone know what the maximum number of characters a decimal field could create (including '.' and '-' characters (I just guessed at 33)?

thanks,

Hi David,

Ops, I fixed above to include ",2". ROUND(number,2) does not work for rounding 2.565 to 2.57 (in VistaDB).

What are you seeing Ron?

I am wondering if the locale of your database would impact that? I don't think so. I can only think of strings, date times, money, I don't think things like round would use anything locale specific.

No, that won't because of the issues we have already talked about (differences in default rounding in .Net compared to VC++ used by SQL Server).

You will have to write custom code to do that if that is the behavior you want. Put it in a CLR Proc if you want it callable from the database, or in your business logic.

Hi David,
With "To Even" (or "Bankers" rounding), it's rounded towards the nearest even whole number.
2.565 = 2.56
2.675 = 2.58
so it's not always rounded down with x.xx5

Here is a TSQL round function for you:

CREATE FUNCTION [MyRound]
(@Operand Decimal,@Places Int)
RETURNS Decimal
AS
BEGIN
declare @x decimal
declare @i int

set @x = @Operand * power(10,@Places)
set @i = @x
set @x = @i + iif((@x - @i) >= .5,1,0)
set @x = @x / power(10,@Places)
Return(@x)
END

Oops I had a bug. See revision above.
Sorry,
Nick