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.
