CODEWITHSUNDEEP
sqlsql-servert-sqldecimalrounding
venkatesh codetree
venkatesh codetree

Reputation: 51

SQL Server custom rounding of decimal value

I want to round the decimal value of 5.457845.

For example: 

5.457845 = 5.0
5.684575 = 5.5

Upvotes: 2

Views: 452

Answers (2)

pcdev
pcdev

Reputation: 3052

How about this:

select floor(5.457845 * 2) / 2  -- returns 5.0
select floor(5.684575 * 2) / 2  -- returns 5.5
select floor(5.0 * 2) / 2       -- corner case 1 - returns 5.0
select floor(5.4999999 * 2) / 2 -- corner case 2 - returns 5.0
select floor(5.5 * 2) / 2       -- corner case 3 - returns 5.5
select floor(5.9999999 * 2) / 2 -- corner case 4 - returns 5.5

Note that this may not work so well for negative numbers:

select floor(-5.0 * 2) / 2       -- corner case 5 - returns -5.0
select floor(-5.4999999 * 2) / 2 -- corner case 6 - returns -5.5
select floor(-5.5 * 2) / 2       -- corner case 7 - returns -5.5
select floor(-5.9999999 * 2) / 2 -- corner case 8 - returns -6.0

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

I don't think any native function is present to do this. Try this trick 

DECLARE @num NUMERIC(22, 6) = 5.684575

SELECT Floor(@num) + CASE WHEN Round(@num, 1, 1) % 1 <= 0.5 THEN 0 ELSE 0.5 END

Result : 5.5

If the number can be negative then you need use ABS function on top of ROUND function

ABS(Round(@num, 1, 1)) % 1

Upvotes: 1

Related Questions