Question

I have a Money column in my SQL Server 2008 table. In my below query how can I round it to nearest 5$

select FineAmount from tickets

Thanks

Answer 1

select round(FineAmount*2,-1)/2 from tickets

or to put nicholaides suggestion in sql

select round(FineAmount/5,0)*5 from tickets

The example assumes that FineAmount is of type money. The second approach is probably better as the first one works with the limit of maximum_value_of_money_type/2

More on ROUND

Answer 2

A general math solution:

Divide by 5, round to the nearest integer, then multiply by 5.

Answer 3

If you want to truncate (round-down) to a grouping of 5 use the modulo function; in Microsoft SQL Server this is %

ie: field1 - (field1 % 5)

If you had field1 == 3, then the calc would be:

3 - (3 % 5) = 0

if it were 13:

13 - (13 % 5) = 10

Simply add 5 if you want to round up

See also MOD is not a recognized built-in function name

Answer 4

My first solution was

create function dbo.udf_RoundToNearest(@x int, @to int)
returns int
with schemabinding as begin
return @to * convert(int, round(convert(float, @x) / convert(float, @to), 0))
end

This works, but is considered by MSSQL as imprecise since it uses floating point numbers internally. That stops it being used in indexed views. You can instead do the job only with integer arithmetic:

create function dbo.udf_RoundToNearest(@x int, @to int)
returns int
with schemabinding as begin

declare @m int
set @m = abs(@x) % abs(@to)

declare @trunc int
set @trunc = abs(@x) - @m

declare @r int
set @r = case when @m * 2 >= abs(@to) then @trunc + abs(@to) else @trunc end

return case when @x < 0 then -@r else @r end

end

Answer 5

Round to next greater 5

(CAST(@Amount/5 AS INT) + IIF(CAST(ROUND(@Amount,0) AS INT) % 5>1,1,0))*5)

Answer 6

DECLARE @Amount DECIMAL(18,3) ; SET @Amount = 7818.32

SELECT(Round((@Amount-CAST(@Amount AS INT))*100 /5,0)*5 /100) 
      + CAST( @Amount AS INT)

-- you ll get 7818.30

友情链接