CODEWITHSUNDEEP
stored-procedures
maideen
maideen

Reputation: 1

How to round the decimal?

I have problem in rounding the number with decimal

search result I only got the following answer. select round(1.12,1) it takes as 1.10. select round(1.15,1) it takes as 1.20.

But I need the result like below.

Ex: 12.34  = 12.30
Ex: 12.35 = 12.35
Ex: 12.36 = 12.40
Ex: 12.95 = 12.95
Ex: 12.96 =13.00

Pls help me

Maideen

Upvotes: 0

Views: 115

Answers (2)

shawnt00
shawnt00

Reputation: 17925

I think this will do what you want (on SQL Server.) And I can't think of a more straightforward way off the top of my head.

round(x, 1, 1) +
    0.05 * case round(x * 100, 0, 1) % 10
        when 6 then 2 when 7 then 2 when 8 then 2 when 9 then 2
        when 5 then 1
        else 0
    end

I thought about it a bit and you may prefer this one instead:

    case round(x * 100, 0, 1) % 10
        when 5 then round(x, 1, 1) + 0.05 -- truncate and add back the 0.05
        else round(x, 1)                  -- round normally
    end

Upvotes: 0

P.R.
P.R.

Reputation: 3917

If I understand the question correctly, and you want to round to 0 / 5 in the two points after the decimal, I would do something like:

out = round(in * 20.0) * 0.05

What that basically does is multiplying your number in with 100 to operate on integers rather than floats. Then you divide by 5 to round to positions of 0 or 5 only (100 / 5 = 20). After rounding you multiply 0.05 back on the result to get your old range back.

You can change round to floor or ceil if that was what you were after.

Upvotes: 1

Related Questions