Round up to the nearest 500 or 1000 in SQL Server [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Round UP t开发者_开发技巧o the nearest 100 in SQL Server

Is it possible to round up a figure to the nearest 500 or 1000 in SQL Server?

Example:

14425.00 -> 14500.00

14585.00 -> 15000.00

---

I'd use this, with data by example

declare @num int
set @num = 749
select (round(((@num+250)/500),0)*500)
Result = 500

or to show it works

declare @num int
set @num = 750
select (round(((@num+250)/500),0)*500)
Result = 1000

however wrapping into a general function would be sensible

CREATE FUNCTION ufnRoundMyValue 
(
    @val int,
    @base int
)
RETURNS int
AS
BEGIN
    declare @res int

    select @res = (round(((@val+(@base/2))/@base),0)*@base) 
    RETURN @res
END
GO

then it's just

select dbo.ufnRoundMyValue(749,500)

wherever you need it

---

Always rounding up:

((value+499)/500)*500

Rounding up or down:

((value+250)/500)*500

---

 SELECT (((col+250)/500)*500) FROM table;