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 Tuesday, August 17, 2010 8:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 1,812, Visits: 6,583
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?
Post #970431
Posted Tuesday, August 17, 2010 8:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Interesting question and good explanation.

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


SQL DBA.
Post #970438
Posted Tuesday, August 17, 2010 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #970442
Posted Tuesday, August 17, 2010 8:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Post #970447
Posted Tuesday, August 17, 2010 8:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:33 PM
Points: 2,414, Visits: 2,826
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


-------------------
"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
Post #970477
Posted Tuesday, August 17, 2010 8:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #970483
Posted Tuesday, August 17, 2010 10:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:15 AM
Points: 1,110, Visits: 4,907
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?
Post #970586
Posted Tuesday, August 17, 2010 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:06 PM
Points: 368, Visits: 1,081
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).
Post #970641
Posted Tuesday, August 17, 2010 2:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Post #970733
Posted Wednesday, August 25, 2010 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:22 PM
Points: 2,262, Visits: 5,421
Coool question, Hugo.. Thanks
Post #974888
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse