CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 639

Round-off if decimal value is .6 or higher

I have this requirement that every decimal values will be round-off, so I used the built-in function ROUND in SQL.

But I have this requirement where below or equal to .5 of decimal values will not be round-off. I have researched on this but can't find any results related to my concern.

Below should be the expected result

SELECT ROUND(200.521231,0) --- 200.000000  
SELECT ROUND(200.436231,0) --- 200.000000  
SELECT ROUND(-200.436231,0) --- -200.000000  
SELECT ROUND(200.621231,0) --- 201.000000

I'm using MS-SQL Server

Upvotes: 1

Views: 197

Answers (1)

Uri Goren
Uri Goren

Reputation: 13700

The trivial thing to do is:

SELECT ROUND(@x -0.1,0)

where @x is the decimal value you're rounding

if @x is allowed to be negative:

SELECT IF( @x>0, ROUND(@x -0.1,0), ROUND(@x +0.1,0))

Upvotes: 3

Related Questions