Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round up or down III


Round up or down III

Author
Message
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 8064
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. So
SET @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?
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Interesting question and good explanation.

For a normal person 3 and 3.0 is same. programming can be manipulative.

SQL DBA.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
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 I did incorrectly.

Again sorry - - I did edit my post so as not to pass on what is now not correct information.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11584
Toreador (8/17/2010)
So why not just make it a Bit datatype and call it 'Truncate' instead of 'Function'? Did it original behave differently?

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)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3035 Visits: 3755
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
Thanks for the question Hugo. Nice continuation in the series.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 Visits: 5009
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?
john.moreno
john.moreno
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1112
jts_2003 (8/17/2010)
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?


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).
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11584
jts_2003 (8/17/2010)
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?

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.

john.moreno (8/17/2010)
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).

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 5545
Coool question, Hugo.. 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