CODEWITHSUNDEEP
sqlsql-server
SuperNova
SuperNova

Reputation: 11

Rounding/Truncate with MS SQL

Having trouble getting the following value to only display 2 decimal places after performing a division calculation. The purpose is to round E.Amount to the nearest .25. The formula does that but returns a value with 6 decimals rather than the desired 2.

Example values of E.Amount are 0.15, 0.02, 2.37

Round(E.Amount/25,2)*25

Other things I've tried

Round(Round(E.Amount/25,2)*25,2) -- Still returns 6 decimals
Cast (Round(E.Amount/25,2)*25) as Numeric (18,2) --returns a syntax error

Other variations of using cast have resulted in E.Amount be the subject of the cast which causes the value to be incorrect.

Upvotes: 1

Views: 125

Answers (2)

Nabav
Nabav

Reputation: 273

How about:

cast( Round(E.Amount/25,2)*25 as decimal(20,2) )

Upvotes: 2

KeithL
KeithL

Reputation: 5594

You can do it kind of manually with a case statement:

declare @value decimal(12,2) = 12.37

select Floor(@Value) 
    + case  when @value - Round(@value,0)<=.12 then 0
            when @value - Round(@value,0)<=.37 then .25
            when @value - Round(@value,0)<=.62 then .5
            when @value - Round(@value,0)<=.87 then .75
            else 1 end

Upvotes: 0

Related Questions