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 12»»

Ceiling, Floor and Round! Expand / Collapse
Author
Message
Posted Tuesday, October 27, 2009 10:33 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item Ceiling, Floor and Round!
Post #809710
Posted Wednesday, October 28, 2009 3:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
I never get trailing zeros on a float unless I use Microsoft SQL Server Management Studio. For the last column, I get 3.6 not 3.6000 when I use any other tool. I wonder why Microsoft finds it necessary to add extra zeros. Don't they realize 3.6 and 3.6000 is the same value and it's more proper to delete the extra zeros?

I'd say they would only be relevant if you're using money. $3.60 is easier to read than $3.6 and helps the numbers line up with the decimal point if you right align them.


Post #809758
Posted Wednesday, October 28, 2009 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 13,020, Visits: 11,826
cengland0 (10/28/2009)
Don't they realize 3.6 and 3.6000 is the same value and it's more proper to delete the extra zeros?


Not sure I agree that it is more "proper". Depending on what the number represents you may want to know that it was calculated at some precision. For example if you were measuring the clearance of a piston to the cylinder it would be more "proper" to say 3.6000 instead of 3.6. As with everything these types of things need to be suited to the task at hand. Just my 2 cents.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #809908
Posted Wednesday, October 28, 2009 7:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:50 AM
Points: 1,542, Visits: 1,841
How about adding this: round(3.5678,1,1)
Post #809923
Posted Wednesday, October 28, 2009 7:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, Visits: 322
slange-862761 (10/28/2009)
cengland0 (10/28/2009)
Don't they realize 3.6 and 3.6000 is the same value and it's more proper to delete the extra zeros?


Not sure I agree that it is more "proper". Depending on what the number represents you may want to know that it was calculated at some precision. For example if you were measuring the clearance of a piston to the cylinder it would be more "proper" to say 3.6000 instead of 3.6. As with everything these types of things need to be suited to the task at hand. Just my 2 cents.


Well I think you are both kind of correct. Trailing zeros are good to show precision, but we just rounded a number so how precise could it be? There may be a reason they chose to leave as many zeros as digits after the decimal. I personally cannot think of why they would do it, but maybe somebody else could think of a good reason.
Post #809940
Posted Wednesday, October 28, 2009 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 13,020, Visits: 11,826
Good point. The real deal is that the datatype was implicity set to have 4 decimal places and the round function will not change the datatype. If you really wanted to remove them you would need to cast them with something like cast(round(3.5678,1) as numeric(2,1)).


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #809946
Posted Wednesday, October 28, 2009 9:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 642, Visits: 400
slange-862761 (10/28/2009)
Good point. The real deal is that the datatype was implicity set to have 4 decimal places and the round function will not change the datatype....

Exactly. You can demonstrate this by, e.g., taking the last digit away from the round() call's 1st argument:
select round(3.567,1)

will return a value rounded to three decimal places because the 1st argument is specified to three decimal places. Thanks.
Post #810055
Posted Wednesday, October 28, 2009 9:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Michael Poppers (10/28/2009)
slange-862761 (10/28/2009)
Good point. The real deal is that the datatype was implicity set to have 4 decimal places and the round function will not change the datatype....

Exactly. You can demonstrate this by, e.g., taking the last digit away from the round() call's 1st argument:
select round(3.567,1)

will return a value rounded to three decimal places because the 1st argument is specified to three decimal places. Thanks.


But as someone else pointed out, the additional zeros can be mistaken for precision which is not good to have when you're rounding numbers.

If I told a manufacturer I needed a bolt .75 inches long, that's not as precise as telling them it needs to be .750000 inches. Without those zeros, they could make the bolt .7502 inches long and still be within specs. Rounding the number to one digit should not have such precision.
Post #810067
Posted Wednesday, October 28, 2009 9:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Also, if I'm rounding a number, I'm trying to reduce the number of digits after the decimal. If it keeps the same number of digits, why round it?

Good example:

Select 123.45 * 1.07

This is how much tax @ 7% to charge a customer and then add the original 123.45 back in. The answer is 132.0915. Now if I need to round that number, I would expect the final dollar amount to be 132.09.

 select round(123.45 * 1.07,2)

This results in 132.0900 -- a little confusing if I'm going to be generating a report showing total sales after tax.

Anyone have a good solution to get the 132.09 as a result of a simple formula?
Post #810075
Posted Wednesday, October 28, 2009 12:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
cengland0 (10/28/2009)
Also, if I'm rounding a number, I'm trying to reduce the number of digits after the decimal. If it keeps the same number of digits, why round it?

Good example:

Select 123.45 * 1.07

This is how much tax @ 7% to charge a customer and then add the original 123.45 back in. The answer is 132.0915. Now if I need to round that number, I would expect the final dollar amount to be 132.09.

 select round(123.45 * 1.07,2)

This results in 132.0900 -- a little confusing if I'm going to be generating a report showing total sales after tax.

Anyone have a good solution to get the 132.09 as a result of a simple formula?


I would use the reporting application to present the desired format. However if you are going to have to output the raw data then you have multiple options. For instance:
SELECT CAST(ROUND(123.45 * 1.07,2) AS numeric(9, 2))



Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #810204
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse