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

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
is not working as i'm expecting. See the below query,

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


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.


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