Round() function doesn't seem to work properly in SQL 2k5

Hi,
I have been using the round function in SQL 2000 for quite a while, and when doing sums of percentages have used the Round(99.99,0) function to great avail to get a value of 100.00.

But, when I migrated to SQL 2K5, i used the same function and it gave me an arithmetic overflow error; more specifically:
"An error occurred while executing batch. Error message is: Arithmetic Overflow."

Is anyone aware of any bugs / changes to this function in 2k5 that I am not aware of?

Thanks,
Saurabh

You might also try using CEILING() in conjuction with ROUND(). But be careful if you do...

|||

Yeah, ceiling will work for 99.99, but for the likes of 100.01, then floor is better.

Anyway, does anyone know why the round doesnt work properly in sql 2k5?

|||

I'm not aware of any changes in the underlaying functionality of ROUND().

Could it be that something about the data is causing the overflow?

|||

Hmm...not sure

It was just a simple select round(99.99,-2) that yielded 100.00 in sql 2k and arithmetic overflow error in 2k5

|||

You got me on that one. I definitely see that the results are the same for SLQ 2000 and SQL 2005.

Hopefully, someone from MS will chime in and let us know what is happening...

|||That seems to be a problem with implicit casting.

This works:
select round(CAST(99.99 AS DECIMAL(10,2)),0)

This errors with "An error occurred while executing batch. Error message is: Arithmetic Overflow":

select round(99.99,0)

It looks like a bug to me.

Running 2005 9.0.2153. Anyone running SP2 to see if this fails under it also?|||

Cool, mate!

That works.

I am running 2005 9.0.2047 at the moment, and have that error.