Round up or down III

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Hugo Kornelis » Round up or down III

29 posts, Page 2 of 3

««1 2 3 »»

Round up or down III

Rate Topic

Display Mode

Topic Options

Author

Message

Toreador

Posted Tuesday, August 17, 2010 8:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 1,356, Visits: 4,761

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

SanjayAttray

Posted Tuesday, August 17, 2010 8:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

Interesting question and good explanation.

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

SQL DBA.

Post #970438

bitbucket-25253

Posted Tuesday, August 17, 2010 8:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 5,099, Visits: 20,190

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

Hugo Kornelis

Posted Tuesday, August 17, 2010 8:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230, Visits: 7,020

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

webrunner

Posted Tuesday, August 17, 2010 8:49 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:05 PM
Points: 2,117, Visits: 2,209

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

-------------------
"The chemistry must be respected." - Walter White

"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

SQLRNNR

Posted Tuesday, August 17, 2010 8:53 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

Thanks for the question Hugo. Nice continuation in the series.

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #970483

jts_2003

Posted Tuesday, August 17, 2010 10:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 1,060, Visits: 4,156

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

john.moreno

Posted Tuesday, August 17, 2010 11:50 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:31 PM
Points: 339, Visits: 950

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

Hugo Kornelis

Posted Tuesday, August 17, 2010 2:45 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230, Visits: 7,020

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

ColdCoffee

Posted Wednesday, August 25, 2010 8:19 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

Coool question, Hugo.. Thanks Smile

Post #974888

« Prev Topic | Next Topic »

29 posts, Page 2 of 3

««1 2 3 »»

Permissions