Sankar Raj Sankar Raj - 3 years ago 97
SQL Question

Round value only if it is greater than 5

I'm not sure that this is a duplicate question.

I need to

ROUND
a value only if the succeeding value is greater than 5.

For example:

If i have 123.4575, then the rounded value should be 123.457.

If i have 123.4576, then the rounded value should be 123.458.

But the default
ROUND
is not working as i'm expecting. See the below query,

select cast(round(123.4575, 3) as decimal(18,3))


Result:


123.458 (where it needs to be 123.457)

I need only three decimal points.

I prefer to suggest me some in-built functions rather than writing functions on my own.

Thanks.

Answer Source

This has been asked (many times). 5 is always rounded up by SQL Server. If you do not want to write your own routine look at Minh's answer here. This shows a neat trick to achieve what you want using a case statement and FLOOR.

Also an easy alternative is to subtract 1 from your number first at a precision 1 greater than your rounding, thus:

SELECT CAST(ROUND(123.4575 - 0.00001, 3) as decimal(18,3)) 

gives 123.457, whilst

SELECT CAST(ROUND(123.4576 - 0.00001, 3) as decimal(18,3)) 

gives 123.458

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download