Round up or down I

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 I

29 posts, Page 1 of 3

1 2 3 »»»

Round up or down I

Rate Topic

Display Mode

Topic Options

Author

Message

Hugo Kornelis

Posted Monday, August 02, 2010 10:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232, Visits: 7,023

Comments posted to this topic are about the item Round up or down I

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #962576

udayroy15

Posted Monday, August 02, 2010 11:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 02, 2013 5:42 AM
Points: 190, Visits: 74

nice question but ans with care

Post #962591

prashant.bhatt

Posted Tuesday, August 03, 2010 12:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 29, 2013 1:52 AM
Points: 118, Visits: 166

Good question...

Prashant Bhatt
Sr Engineer - Application Programming

Post #962620

cengland0

Posted Tuesday, August 03, 2010 3:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299

So you need to cast it like this?

round(cast(5 as float) / cast(3 as float),1)

Post #962662

ziangij

Posted Tuesday, August 03, 2010 3:33 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:53 AM
Points: 1,526, Visits: 359

even this works...

set @result = round(cast(5 as float) / 3,1)

i guess either the numerator or denominator should be in the proper format.

cengland0 (8/3/2010)

So you need to cast it like this?

round(cast(5 as float) / cast(3 as float),1)

Post #962666

Hugo Kornelis

Posted Tuesday, August 03, 2010 3:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232, Visits: 7,023

cengland0 (8/3/2010)

So you need to cast it like this?

round(cast(5 as float) / cast(3 as float),1)

As ziangij already says, casting one of the operands is sufficient. And if you do use explicit casting, I'd suggest you to cast to the datatype of the result: decimal(5,2) instead of float.

You can also use implicit casting: ROUND(5.0/3, 1)
The additional .0 forces SQL Server to treat is as numeric. (Even the trailing zero can be omitted, but for readability I prefer "5.0" over "5.")

And to follow up on a promise I made earlier to people who prefer code in a copy/paste-able format, here it is:

DECLARE @Result decimal(5,2);

SET @Result = ROUND(5/3, 1);

PRINT @Result;

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #962673

Posted Tuesday, August 03, 2010 4:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555

It surprises me why round() acts like floor() when numbers are integers.

I usually prefer the denominator having the ".0" but it is only a matter of preference as the numerator can be ".0".

Post #962706

Hugo Kornelis

Posted Tuesday, August 03, 2010 5:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232, Visits: 7,023

Open Minded (8/3/2010)

It surprises me why round() acts like floor() when numbers are integers.

That is not the case. ROUND() does not act like FLOOR().
The "problem" here is the order or evaluation. The expression "5/3" is evaluated first. Since this division uses integers only, the result is integer. This is where results are truncated instead of rounded, so 5/3 evaluates to 1.

The ROUND then rounds 1 to one decimal place, which leaves the value unchanged (of course).
To verify that ROUND does really round correctly, even with integers, run SELECT ROUND(38, -1)

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #962712

ronmoses

Posted Tuesday, August 03, 2010 5:35 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:09 AM
Points: 648, Visits: 684

Yep, I've been bitten by that one more than once. Until you learn that int/int "gotcha" it can be nearly impossible to troubleshoot a script that is experiencing it. Good question.

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Post #962721

CavyPrincess

Posted Tuesday, August 03, 2010 6:03 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:40 AM
Points: 596, Visits: 1,437

Like ronmoses, I, too, knew the answer because I learned the hard way. It's tough to figure-out the first time, but then the second, third, etc, it finally sinks in...

The example is as simple as this - if using hardcoded values.

set @result = round(5/3.0, 1);

Cindy

Post #962728

« Prev Topic | Next Topic »

29 posts, Page 1 of 3

1 2 3 »»»

Permissions