SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ceiling, Floor and Round!


Ceiling, Floor and Round!

Author
Message
VM-723206
VM-723206
Right there with Babe
Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)

Group: General Forum Members
Points: 776 Visits: 267
Comments posted to this topic are about the item Ceiling, Floor and Round!
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26472 Visits: 17557
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.

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)
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1950
How about adding this: round(3.5678,1,1)
JF1081
JF1081
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26472 Visits: 17557
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.

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)
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 416
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.
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
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.
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
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?
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1499
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search