CODEWITHSUNDEEP
sqloracle-databaserounding
legendefs
legendefs

Reputation: 53

Round to .5 or 1.0 in Oracle SQL

I'm looking to round values like

2.39 -> 2.40

4.66 -> 4.70

2.11 -> 2.15

2.10 -> 2.10

2.50 -> 2.50

5.89 -> 5.90

How can I manage this in SQL?

Thanks

Upvotes: 2

Views: 118

Answers (1)

legendefs
legendefs

Reputation: 53

problem solved : 

SELECT FLOOR ( (1.11 + 0.04) * 20) / 20 FROM DUAL;

test: 

WITH t (my_number)
     AS (SELECT 3.1001 FROM DUAL
         UNION ALL
         SELECT 2.39 FROM DUAL
         UNION ALL
         SELECT 4.66 FROM DUAL
         UNION ALL
         SELECT 2.11 FROM DUAL
         UNION ALL
         SELECT 2.10 FROM DUAL
         UNION ALL
         SELECT 2.50 FROM DUAL
         UNION ALL
         SELECT 5.89 FROM DUAL)
SELECT my_number, FLOOR ( (my_number + 0.04) * 20) / 20 round_on_number
  FROM t;

3.1001  3.1
2.39    2.4
4.66    4.7
2.11    2.15
2.1     2.1
2.5     2.5
5.89    5.9

Upvotes: 2

Related Questions