October 27, 2009 at 10:33 pm
Comments posted to this topic are about the item Ceiling, Floor and Round!
October 28, 2009 at 3:01 am
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.
October 28, 2009 at 7:26 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2009 at 7:35 am
How about adding this: round(3.5678,1,1)
October 28, 2009 at 7:52 am
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.
October 28, 2009 at 7:57 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2009 at 9:28 am
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.
October 28, 2009 at 9:38 am
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.
October 28, 2009 at 9:46 am
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?
October 28, 2009 at 12:13 pm
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.07This 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))
October 28, 2009 at 12:16 pm
I'm sure someone else can post it more elegantly, but here's a quick solution:
select cast(round(123.45 * 1.07,2) as money)
I based it off of slange-862761's suggestion of using cast in an earlier post.
I don't know if this helps at all, but it might point you in the right direction.
--
Kevin C.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply