How can I round a decimal to the nearest even number in SQL?

Question

Hi,

I need to round a decimal in a sql query on Oracle 10g to the nearest even number. If the number is even, it should be returned. If the number is odd, the next even number should be returned.

This is what I want: 8.05 should return 8.06, 3.48 should return 3.48

How can I do this?

Thanks, Andrew

Answer 1

If you want to round e.g. to the second decimal even digit, you can do something like that: select round(3.43 / 0.02, 0) * 0.02; that will produce 3.44.

This can be extended as you wish: e.g. first decimal digit which is multiple of 3: select round(3.5452234 / 0.3, 0) * 0.3; will give 3.6.

Answer 2

I really don't understand the logic of incorrectly rounding numbers. Additionally, this cannot easily because you aren't dealing with integers. However, if you really need to, I would suggest following something like this pseudo-code.

if ((num / 2) * 2 = num) {
    return // number is even
    }
else {
    num = num + .01 // this assumes you are only working with two decimal points.
}