# Floor, Round, and Ceiling

5

October 5, 2016 by Kenneth Fisher

I saw an interesting question today about rounding. Specifically they always wanted to round down. Now as it happens that wasn’t really what they needed, but we can get to that later. It did get me thinking and I figured it would make a good post.

##### ROUND

This is the most complicated of the three. It does a standard rounding. If value is .5 or over then you get back 1. If it’s less than .5 you get back 0. On top of that you get to pass the place you want to round to. So for example 0 rounds to the nearest ones place, -1 rounds to the tens place, 2 rounds to the hundredths.

```SELECT
ROUND(13.567,0) 	-- 14.000  -- Ones place
ROUND(13.567,-1),	-- 10.000  -- Tens place
ROUND(13.567,2) 	-- 13.570  -- Hundredths place```

Then on top of that you can pass a third optional parameter that causes it to truncate to that position rather than round.

```SELECT
ROUND(13.567,0,1)	-- 13.000  -- Ones place
ROUND(13.567,-1,1),	-- 10.000  -- Tens place
ROUND(13.567,2,1) 	-- 13.560  -- Hundredths place```

The value returned is a similar data type as the one passed in. Same precision and scale but TINYINT becomes INT for example.

Oddly ROUND(x,0,1) returns almost the same value as FLOOR.

##### FLOOR

Floor returns the integer value less than or equal to the value passed in.  Very similar to ROUND(x,0,1). But while ROUND returns the same scale (where possible) as the data type passed in, the data type FLOOR returns has a 0 scale (where possible).

```SELECT
FLOOR(13.5),	-- 13
FLOOR(13.8),	-- 13
FLOOR(13.2) 	-- 13```

##### CEILING

Ceiling, on the other hand, is the opposite. It returns the same data type as floor (0 scale where possible) but returns the integer equal to or higher than the value passed in.

```SELECT
CEILING(13.5),	-- 14
CEILING(13.8),	-- 14
CEILING(13.2) 	-- 14```

##### Rounding to other values

Now back to that original question!

The OP (original poster) was actually wanting to round to the nearest 500,000. So how do we do that? 100,000 wouldn’t be hard at all but rounding to the nearest 5 is a bit more difficult. Well, ok, not that much more difficult. Simply divide by the value you want to round to, round, then multiply the value back.

Probably easier to see in action

```SELECT
ROUND(12345/500.0,0)*500, -- 12500
ROUND(12345/300.0,0)*300, -- 12300
ROUND(12345/700.0,0)*700  -- 12600```

One last note, in order for the ROUND to work correctly the value you divide by has to have a decimal. Without it you basically get a FLOOR.

## 5 thoughts on “Floor, Round, and Ceiling”

1. Rich Slabbekoorn says:

Your description of FLOOR is incorrect. It works similar to CEILING in that it returns the integer equal to or lower than the value passed in. This distinction only makes a difference with negative numbers.

SELECT
ROUND(-13.5, 0, 1), — -13.0
FLOOR(-13.5), — -14
CEILING(-13.5) — -13

2. […] The answer is here […]

3. simplehawk says:

Would love if you put in a note that Ceiling doesn’t work without one of the numbers being a decimal. For example CEILING(83/30) returns 2, not 3. CEILING(83/30.0) returns 3.

• Technically you just did 🙂

Now, why that works is kind of interesting. If you check 83/30 on it’s own you’ll notice that you get 2 even without the CEILING. That’s because those are integers and there is no decimal involved. And when you are working with integers it always acts like you are using floor. No rounding involved at all. The minute you make either value a decimal then implicit conversion kicks in and the other is converted to a decimal, your output is a decimal and CEILING actually has something to do.

This site uses Akismet to reduce spam. Learn how your comment data is processed.