Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Round up or down III Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, August 29, 2010 7:14 AM
 SSCrazy Group: General Forum Members Last Login: Friday, April 11, 2014 1:51 PM Points: 2,628, Visits: 760
 Agreed - Hugo, great question. I'm fascinated with the way the round works in SQL. Jamie
Post #977071
 Posted Sunday, September 12, 2010 6:22 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 7:47 AM Points: 8,296, Visits: 8,750
 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 (http://msdn.microsoft.com/en-us/library/ms175003.aspx). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment. Tom
Post #984367
 Posted Sunday, September 12, 2010 6:42 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 10:59 AM Points: 5,801, Visits: 8,018
 Tom.Thomson (9/12/2010)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 (http://msdn.microsoft.com/en-us/library/ms175003.aspx). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.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:`SELECT ROUND(5/3.0, 1, 2) AS aINTO b;goEXEC sp_help b;goDROP TABLE b;go`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) Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #984370
 Posted Sunday, September 12, 2010 1:16 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 7:47 AM Points: 8,296, Visits: 8,750
 Hugo Kornelis (9/12/2010)However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:`SELECT ROUND(5/3.0, 1, 2) AS aINTO b;goEXEC sp_help b;goDROP TABLE b;go`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?)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 (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.90).aspx) says that the result of ROUND has the same type as the expression to be rounded, as in your explanation. The 2008 version (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.100).aspx) and the current version (http://msdn.microsoft.com/en-us/library/ms175003.aspx) 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. Tom
Post #984425
 Posted Monday, September 13, 2010 2:20 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 7:53 AM Points: 1,659, Visits: 6,008
 Hugo Kornelis (9/12/2010)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?On SQL 2008 it is also numeric(8,6)
Post #984578
 Posted Friday, April 22, 2011 12:33 PM
 SSChasing Mays Group: General Forum Members Last Login: Sunday, March 04, 2012 4:02 AM Points: 660, Visits: 134
 So... in the following code ` declare @result decimal(12,8);set @result = round (5/3.0, 4,3);select @result;`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?
Post #1097581
 Posted Monday, April 25, 2011 10:08 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, March 27, 2014 8:50 AM Points: 2,163, Visits: 2,184
 terrykzncs (4/22/2011)So... in the following code ` declare @result decimal(12,8);set @result = round (5/3.0, 4,3);select @result;`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?It is because of the extra ",3" in your ROUND call. `set @result = round (5/3.0, 4,3);`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.:`declare @result decimal(12,8);set @result = round (5/3.0, 4);select @result;`
Post #1098179
 Posted Wednesday, June 01, 2011 4:31 AM
 Old Hand Group: General Forum Members Last Login: Thursday, April 10, 2014 9:51 PM Points: 371, Visits: 111
 nice question but setting arithabort on gives completely diff solution
Post #1117948
 Posted Wednesday, March 07, 2012 2:04 PM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, February 11, 2014 9:09 AM Points: 483, Visits: 242
 Nice question and resources on the rounding. Thanks.
Post #1263273

 Permissions