SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round up or down III


Round up or down III

Author
Message
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3146 Visits: 838
Agreed - Hugo, great question. I'm fascinated with the way the round works in SQL.

Jamie
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14538 Visits: 12238
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

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11186 Visits: 12004
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
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14538 Visits: 12238
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

Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 8084
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)
terrykzncs
terrykzncs
Say Hey Kid
Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)

Group: General Forum Members
Points: 700 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?Unsure
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2718 Visits: 2204
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?Unsure


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;


khullargirish02
khullargirish02
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 126
nice question but setting arithabort on gives completely diff solution
zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 259
Nice question and resources on the rounding. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search