Rounding Up to the Nearest 1,000

A co-worker came over and asked how to round up to the nearest 1,000.  He tried to use ROUND but found that it always split between going up or going down depending on the value and the report he was working on always had to round up.

Almost before he finished asking, actually before he finished asking, I said, “CEILING”.  Books Online defines the CEILING function as the following:

Returns the smallest integer greater than, or equal to, the specified numeric expression.

A quick example from Books Online:

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO

The example provides the following output:

It’s a fairly simple function, but there isn’t an option to break this out to the tens, hundreds, or thousands.  To do this, I suggested he modify the base value that he needed by 1,000 to get the CEILING value and then multiply it back by the base value of 1,000.

The following is the example code that I showed him:

DECLARE @Value decimal(12,3)
SET @Value = 9321.12
SELECT @Value as Original
,CEILING(@Value/1000)*1000 as Ceiling_Pos
,CEILING(-@Value/1000)*1000 as Ceiling_Neg
,ROUND(@Value, -3) as Round_Pos
,ROUND(-@Value, -3) as Round_Neg

In the second column, the CEILING function produced the result he was looking for:

I included the use of the ROUND function to show that it did not produce the required result.  This method also works if you need to round up to the nearest 250, 50, 5, or 333rd.  Ok… it only mostly works with the 333rd and has a rounding issue, but that would just be a goofy requirement so I can live with that.

Hopefully, this can help others with their rounding up needs.