Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Round up or down I Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, August 02, 2010 10:13 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:57 PM Points: 5,594, Visits: 7,789
 Comments posted to this topic are about the item Round up or down I Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962576
 Posted Monday, August 02, 2010 11:15 PM
 SSC Veteran Group: General Forum Members Last Login: Monday, September 09, 2013 7:57 AM Points: 267, Visits: 93
 nice question but ans with care
Post #962591
 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 BhattSr Engineer - Application Programming
Post #962620
 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
 Posted Tuesday, August 03, 2010 3:33 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:53 AM Points: 1,710, Visits: 367
 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
 Posted Tuesday, August 03, 2010 3:46 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:57 PM Points: 5,594, Visits: 7,789
 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 MVPVisit 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, October 10, 2013 9:40 PM Points: 174, Visits: 558
 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
 Posted Tuesday, August 03, 2010 5:08 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:57 PM Points: 5,594, Visits: 7,789
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962712
 Posted Tuesday, August 03, 2010 5:35 AM
 Right there with Babe Group: General Forum Members Last Login: Monday, December 02, 2013 7:12 AM Points: 742, Visits: 785
 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 zeroNULL is not an empty stringNULL is the unknown
Post #962721
 Posted Tuesday, August 03, 2010 6:03 AM
 SSChasing Mays Group: General Forum Members Last Login: Monday, November 25, 2013 6:14 AM Points: 613, Visits: 1,473
 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

 Permissions