Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Round up or down III Expand / Collapse
Author
Message
Posted Sunday, August 29, 2010 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 7,694, Visits: 9,423
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:36 PM
Points: 6,002, Visits: 8,267
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 a
INTO b;
go
EXEC sp_help b;
go
DROP 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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #984370
Posted Sunday, September 12, 2010 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 7,694, Visits: 9,423
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 a
INTO b;
go
EXEC sp_help b;
go
DROP 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 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.66660000

The 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.66660000

The 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 1, 2011 4:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:18 AM
Points: 375, Visits: 116
nice question but setting arithabort on gives completely diff solution
Post #1117948
Posted Wednesday, March 7, 2012 2:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Nice question and resources on the rounding. Thanks.
Post #1263273
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse