SQL2005 Round

James
I got 0 in SS2005(SP3)
SELECT ROUND(1/2,0)

SELECT FLOOR(0.5)



"James Walker" wrote in message

> I'm currently creating a stored procedure that needs to round a number to
> a specified "fraction". The stored procedure is being taken from a VB6
> function.
>
> I have everything working fine with one exception, the Round function.
> When VB6 rounds 0.5 (or exactly 1/2), it rounds down to 0 (zero), however
> SQL 2005 rounds 0.5 (or exactly 1/2) up to 1.
>
> In both places I'm using ROUND(number, 0).
>
> Any ideas how to get SQL to round 0.5 down to zero?
>
> Thanks,
> James Walker
>
 >> Stay informed about: SQL2005 Round 

Thanks for the replies, however I should have explained in a little more
detail.

The "number" I'm rounding is the numerator of a fraction. I'm passing in the
denominator to round to.

As an example my number could be 5.03125 and round to .0625 (1/16). Where
@Dimension is 5.03125 and @RoundTo is .0625

Where is my sql statement:

FLOOR(@Dimension) + ROUND((@Dimension - FLOOR(@Dimension)) * @RoundTo, 0) /
@RoundTo



I would a result of 5, however the above statement returns 5.0625.

Thanks,
James Walker



"Uri Dimant" wrote in message

> James
> I got 0 in SS2005(SP3)
> SELECT ROUND(1/2,0)
>
> SELECT FLOOR(0.5)
>
>
>
> "James Walker" wrote in message
>
>> I'm currently creating a stored procedure that needs to round a number to
>> a specified "fraction". The stored procedure is being taken from a VB6
>> function.
>>
>> I have everything working fine with one exception, the Round function.
>> When VB6 rounds 0.5 (or exactly 1/2), it rounds down to 0 (zero), however
>> SQL 2005 rounds 0.5 (or exactly 1/2) up to 1.
>>
>> In both places I'm using ROUND(number, 0).
>>
>> Any ideas how to get SQL to round 0.5 down to zero?
>>
>> Thanks,
>> James Walker
>>
>
>
 >> Stay informed about: SQL2005 Round 

If I run this I get 5 as result:

DECLARE @Dimension DECIMAL(12, 6) = 5.03125;
DECLARE @RoundTo DECIMAL(12, 6) = 0.0625;

SELECT FLOOR(@Dimension) +
ROUND((@Dimension - FLOOR(@Dimension)) * @RoundTo, 0) /
@RoundTo;


--------------
5.000000000000

--
Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: SQL2005 Round 

But seems your rounding logic is still incorrect. Take a look at the
following article and the custom rounding functions that are provided:
http://support.microsoft.com/kb/196652

--
Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: SQL2005 Round