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.

Fill in your details below or click an icon to log in:

Notify me of new comments via email.

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

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

Enter your email address to follow this blog and receive notifications of new posts by email.

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.

The OP (original poster) was actually wanting to round to the nearest 500,000. So how do we do that? 100,000 wouldnt 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.

You are commenting using your account.(LogOutChange)

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

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

You are commenting using your Twitter account.(LogOutChange)

Thanks! Ill get it fixed asap ?

You are commenting using your Facebook account.(LogOutChange)

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 its 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.

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.

Category:Microsoft SQL ServerSQLServerPedia SyndicationT-SQLTags:microsoft sql serverT-SQL

I saw aninteresting question today about. Specifically they always wanted to round. Now as it happens that wasnt 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.

You are commenting using your Google+ account.(LogOutChange)