SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / Round up or down III / Latest Posts

RE: Round up or down III

zymos — Wed, 07 Mar 2012 14:04:13 GMT

Nice question and resources on the rounding. Thanks.

RE: Round up or down III

khullargirish02 — Wed, 01 Jun 2011 04:31:05 GMT

nice question but setting arithabort on gives completely diff solution

RE: Round up or down III

UMG Developer — Mon, 25 Apr 2011 10:08:55 GMT

[quote][b]terrykzncs (4/22/2011)[/b][hr]So... in the following code [code] declare @result decimal(12,8);set @result = round (5/3.0, 4,3);select @result;[/code]I get 1.66660000The operation (5/3.0) completes and the rounding is done according to the variable declared (decimal(12,8) - 1.66666667 - and then truncated to 4 places - 1.6666?Sorry to be a pain, but how is it that I get 1.66660000 instead of 1.66670000? Which step decides if I get the 7 or 6 at the end?:unsure:[/quote]It is because of the extra ",3" in your ROUND call. [code="sql"]set @result = round (5/3.0, 4[b],3[/b]);[/code]The third parameter tells round to truncate instead of round if it is non-zero.Try this and I think you will get what you are looking for.:[code="sql"]declare @result decimal(12,8);set @result = round (5/3.0, 4);select @result;[/code]

RE: Round up or down III

terrykzncs — Fri, 22 Apr 2011 12:33:12 GMT

So... in the following code [code] declare @result decimal(12,8);set @result = round (5/3.0, 4,3);select @result;[/code]I get 1.66660000The operation (5/3.0) completes and the rounding is done according to the variable declared (decimal(12,8) - 1.66666667 - and then truncated to 4 places - 1.6666?Sorry to be a pain, but how is it that I get 1.66660000 instead of 1.66670000? Which step decides if I get the 7 or 6 at the end?:unsure:

RE: Round up or down III

Toreador — Mon, 13 Sep 2010 02:20:17 GMT

[quote][b]Hugo Kornelis (9/12/2010)[/b][hr]On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?[/quote]On SQL 2008 it is also numeric(8,6)

RE: Round up or down III

L' Eomot Inversé — Sun, 12 Sep 2010 13:16:20 GMT

[quote][b]Hugo Kornelis (9/12/2010)[/b][hr]However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:[code="sql"]SELECT ROUND(5/3.0, 1, 2) AS aINTO b;goEXEC sp_help b;goDROP TABLE b;go[/code]On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?)[/quote]that intrigued me, so I did some more documentation checking.It appears that there was a change between SQLS 2005 and SQL S2008; the 2005 version of BoL ([url]http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.90).aspx[/url]) says that the result of ROUND has the same type as the expression to be rounded, as in your explanation. The 2008 version ([url]http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.100).aspx[/url]) and the current version ([url]http://msdn.microsoft.com/en-us/library/ms175003.aspx[/url]) both say that for neumerics and decimals the result type is decimal with precision 38. So the explanation you gave was apparently correct for SQL 2005, and incorrect for SQL 2008.I haven't been able to check the 2008 behavious as I don't yet have SQL on the machine I have with me and am having difficulty getting hold of SQL server at a sensible price. My usual UK suppliers are quoting more for shipping a disc here than the total for licence, disc, and shipping if I were in the UK, so the total price ends up about 2.5 times what it would have been if I'd fixed it last week before I flew out; so I'm looking for a local supplier and in parallel trying to persuade of of the usual suppliers to ship be airmail instead of using UPS or any of the other overpriced and unreliable delivery companies that are so much in vogue in the computing industry; of course if I were in the US I could just buy online and download, but MS doesn't give me that option here. The people I buy other stuff from [books, audio discs, dvd discs] ship to here cheaply using the international mail system instead of fashionable private delivery companies, so I foolishly expected the same from software vendors. I'll try to remember to run your test code when I get the license sorted and the software installed.

RE: Round up or down III

Hugo Kornelis — Sun, 12 Sep 2010 06:42:12 GMT

[quote][b]Tom.Thomson (9/12/2010)[/b][hr]I think there's a small error in the explanation here. Round never returns a result of type decimal(8,6) because every decimal result that it returns has precision 38 - the datatype for decimal and numeric types is changed by round to decimal(38,s) where s is the scale of the original type. This is documented quite clearly in BoL ([url]http://msdn.microsoft.com/en-us/library/ms175003.aspx[/url]). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.[/quote]Hi Tom,Thanks for catching that. I can not believe I actually overlooked that (though the hyperlink is damaged, I did include a link to that article in the explanation).However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:[code="sql"]SELECT ROUND(5/3.0, 1, 2) AS aINTO b;goEXEC sp_help b;goDROP TABLE b;go[/code]On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?(Of course, for this particular question it does not really matter; there is no overflow so the numeric(38,6) would just add 30 extra leading zeroes and not affect the result - but now that you found this, I do want to go to the bottom of it;-))

RE: Round up or down III

L' Eomot Inversé — Sun, 12 Sep 2010 06:22:55 GMT

I think there's a small error in the explanation here. Round never returns a result of type decimal(8,6) because every decimal result that it returns has precision 38 - the datatype for decimal and numeric types is changed by round to decimal(38,s) where s is the scale of the original type. This is documented quite clearly in BoL ([url]http://msdn.microsoft.com/en-us/library/ms175003.aspx[/url]). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.

RE: Round up or down III

Jamie Longstreet-481950 — Sun, 29 Aug 2010 07:14:14 GMT

Agreed - Hugo, great question. I'm fascinated with the way the round works in SQL.

RE: Round up or down III

ColdCoffee — Wed, 25 Aug 2010 08:19:34 GMT

Coool question, Hugo.. Thanks :-)

RE: Round up or down III

Hugo Kornelis — Tue, 17 Aug 2010 14:45:22 GMT

[quote][b]jts_2003 (8/17/2010)[/b][hr]So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?[/quote]No. The third argument is to truncate instead of rounding. Rounding can be both up and down (when rounding to the integer, 1.7 will be rounded up but 1.3 will be rounded down). But truncation can also have the effect of rounding either up or down - truncating 1.7 will result in 1 (down), but truncating -1.7 will result in -1 (up).To round up or down, use CEILING() and FLOOR(). To round according to normal rounding rules, use ROUND() with the default function. And to truncate, use ROUND() with the non-default function.[quote][b]john.moreno (8/17/2010)[/b][hr]Nope. It truncates and then does the rounding. ROUND(5/3.0, 3,2) would have produced a result of 1.67 (up from the nominal value of 1.666666).[/quote]The ROUND() function with the non-zero function actually only truncates. The result of ROUND(5/3.0, 3, 2) is 1.666000 - truncated to the third decimal. This can be verified by running SELECT ROUND(5/3.0, 3, 2);When you assign that result to a variable that is declared to have two decimal places (as in the code used in the QotD), the assignment forces an implicit conversion. Normal rounding rules apply, so the result will then be 1.67. But that is not related to the ROUND() function itself.

RE: Round up or down III

john.moreno — Tue, 17 Aug 2010 11:50:58 GMT

[quote][b]jts_2003 (8/17/2010)[/b][hr]So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?[/quote]Nope. It truncates and then does the rounding. ROUND(5/3.0, 3,2) would have produced a result of 1.67 (up from the nominal value of 1.666666).

RE: Round up or down III

jts_2003 — Tue, 17 Aug 2010 10:25:24 GMT

So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?

RE: Round up or down III

SQLRNNR — Tue, 17 Aug 2010 08:53:28 GMT

Thanks for the question Hugo. Nice continuation in the series.

RE: Round up or down III

webrunner — Tue, 17 Aug 2010 08:49:42 GMT

I went 0 for 3 on the rounding questions. Scary. Well, what was really scary was that I didn't read through the ROUND documentation after the first question, which no doubt would have helped for the next 2 questions I encountered. I will make sure to read through the references more carefully from now on.They were all great questions, though, and progressed in a way where a new twist was introduced for each one. Nice work.Thanks,webrunner

RE: Round up or down III

Hugo Kornelis — Tue, 17 Aug 2010 08:21:18 GMT

[quote][b]Toreador (8/17/2010)[/b][hr]So why not just make it a Bit datatype and call it 'Truncate' instead of 'Function'? Did it original behave differently?[/quote]Good question. And I have no idea.I did check in the SQL Server 2000 version of Books Online (the oldest version I was able to find online), and it was the same then.Maybe it's for ANSI compliance? (the SQL Server BIT datatype is not ANSI-standard)

RE: Round up or down III

bitbucket-25253 — Tue, 17 Aug 2010 08:18:57 GMT

Hugo Kornelis Copied and tested your code and got your answers. Unfortunately I did not save my test code and now can not duplicate my results, and I tested that not less than 5 times ......... darn if I know the difference ..My apologies to all ...... will do some further investigation to see if I can discover what [b] I did incorrectly.[/b]Again sorry - - I did edit my post so as not to pass on what is now not correct information.

RE: Round up or down III

SanjayAttray — Tue, 17 Aug 2010 08:16:05 GMT

Interesting question and good explanation.For a normal person 3 and 3.0 is same. programming can be manipulative.

RE: Round up or down III

Toreador — Tue, 17 Aug 2010 08:08:40 GMT

Interesting.Can anyone explain exactly what the 3rd argument of Round() is for?The BOL says"function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated."which in practice seems to mean that 0 means round, any other value means truncate. SoSET @Result = ROUND(5/3.0, 1, 2);SET @Result = ROUND(5/3.0, 1, 1);SET @Result = ROUND(5/3.0, 1, 999999999);SET @Result = ROUND(5/3.0, 1, -12345.6789);all do the same thing.So why not just make it a Bit datatype and call it 'Truncate' instead of 'Function'? Did it original behave differently?

RE: Round up or down III

Hugo Kornelis — Tue, 17 Aug 2010 07:14:19 GMT

[quote][b]bitbucket-25253 (8/17/2010)[/b][hr]For a completly different answer - execute the code with the setting forARITHABORT ON and note the difference. An important fact to be aware of.[/quote]Huh? I executed this code:[code="sql"]SET ARITHABORT ON;DECLARE @Result decimal(5,2);SET @Result = ROUND(5/3.0, 1, 2);PRINT @Result;goSET ARITHABORT OFF;DECLARE @Result decimal(5,2);SET @Result = ROUND(5/3.0, 1, 2);PRINT @Result;[/code]And I got 1.60 as both results. What did you get?

RE: Round up or down III

Daniel Bowlin — Tue, 17 Aug 2010 06:42:59 GMT

Great question, thanks. If I keep seeing order of precedence questions on QoTD, I will eventually have it memorized, and won't need to look it up any more.

RE: Round up or down III

bitbucket-25253 — Tue, 17 Aug 2010 06:39:07 GMT

For a completly different answer - execute the code with the setting forARITHABORT ON and note the difference. An important fact to be aware of.Added after reading Hugo Kornelis Posted Today @ 9:14 AM postingCopied and tested his code and got his answers. Unfortunately I did not save my test code and now can not duplicate my results, and I tested not less than 5 times ......... darn if I know the difference ..[B]SO ALL PLEASE DISREGARD MY COMMENTS ABOUT GETTING A DIFFERENT ANSWER WITH ARITHABORT SETTING![/B]

RE: Round up or down III

Tom Garth — Tue, 17 Aug 2010 06:30:10 GMT

Good one. Made me look it up.Thanks.

RE: Round up or down III

jcrawf02 — Tue, 17 Aug 2010 06:27:56 GMT

Good question Hugo, learned something new.

RE: Round up or down III

Yuvaraj S-467280 — Tue, 17 Aug 2010 02:36:21 GMT

interesting...

RE: Round up or down III

Christian Buettner-167247 — Tue, 17 Aug 2010 00:26:59 GMT

Very good as usual:)

RE: Round up or down III

Koen Verbeeck — Mon, 16 Aug 2010 23:52:54 GMT

Nice question, thanks. A good follow up of the previous rounding questions.And a great explanation.

RE: Round up or down III

UMG Developer — Mon, 16 Aug 2010 22:32:41 GMT

Nice question, thanks! I do find it interesting that they combined rounding and truncating in one function...

Round up or down III

Hugo Kornelis — Mon, 16 Aug 2010 21:48:11 GMT

Comments posted to this topic are about the item [B]Round up or down III[/B]