CODEWITHSUNDEEP
sql-serversql-server-2008rounding
Novice
Novice

Reputation: 558

Round off to next 0.05 in SQL Server

I have a requirement, where I need the rates to be rounded off to nearest nickel. For e.g:

Rate . . . . Rounded Value
1.90 . . . . 1.90
1.91 . . . . 1.95
1.92 . . . . 1.95
1.93 . . . . 1.95
1.94 . . . . 1.95
1.95 . . . . 1.95
1.96 . . . . 2.00
1.97 . . . . 2.00
1.98 . . . . 2.00
1.99 . . . . 2.00
2.00 . . . . 2.00

i.e if the 'hunderedth' place after decimal has to be rounded off to its next 0.05.
I have written a query which gives me value for nearest 0.05 not to next 0.05. 

select Rate, (Round((Rate)/0.25 , 2)*0.25) as RoundRate   from ProposedProductPrice order by created desc

Upvotes: 5

Views: 1755

Answers (1)

Cato
Cato

Reputation: 3701

Use ceiling, it is basically a round up command. You can multiply by 20, since a nickel is 20th of a dollar - then divide back down by 20:

select ceiling(Rate * 20) / 20;

Upvotes: 7

Related Questions