September 8, 2013 at 2:01 pm
Is there a round down function?
September 8, 2013 at 2:31 pm
Depends what you mean. There's a function called floor which rounds down to an integer (eg floor(2.5) is 2, floor(-2.5) is -3) so if you want to round down to an integer you can use that.
If you want to round to some number of decimal places after the decimal point, you are out of luck; but if you are only interested in positive values, you can use the round function, specifying what's to be rounded, how many decimal places in the result, and whether to round closest or round towards zero.
round(1.246,2,0) is 1.25 which is not rounding down.
round(1.246,2,255) is 1.24 which looks like rounding down, but
round(-1.246,2,255) is -1.24, not -1.25,
so it's actually rounding towards zero, which is up for negative quantities and down for positive.
The second parameter of round is number of places after decimal point; this can be negative, eg -2 means the last 2 places before the decimal point must be zero which would be rounding to a multiple of 100.
The third parameter specifies which type of rounding is required; 0 means round to nearest, any other value means round towards zero: the third parameter can be anything from 0 to 255 inclusive.
The first parameter is of course the value to be rounded.
Tom
September 9, 2013 at 7:24 am
You can get the result you need: the specific method depends on specifically what you need to do. If you can provide sample values and the desired results, we can take it from there.
Btw, ROUND(1.246, 1) will always result in 1.200, since you're ROUNDing to one decimal place.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 9, 2013 at 10:48 am
ScottPletcher (9/9/2013)
You can get the result you need: the specific method depends on specifically what you need to do. If you can provide sample values and the desired results, we can take it from there.Btw, ROUND(1.246, 1) will always result in 1.200, since you're ROUNDing to one decimal place.
Yes. Thanks for pointing that out.
I've edited the post to change those 1s to 2s. Don't know how I managed to put 1s in there.
Tom
September 9, 2013 at 6:46 pm
In SQL 2008 R2:
SELECT ROUND(123.5555, 0, 1) -- Truncate to integer
,ROUND(123.5555, 1, 1) -- Truncate to first decimal digit
,ROUND(123.5555, 2, 1) -- Truncate to second decimal digit
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply