Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Always rounding up

Posted on 2005-03-18
6
Medium Priority
?
332 Views
Last Modified: 2008-03-06
Hi,

We need to always round up whether it is 0.5 and up, or 0.4 and below. Could someone tell us how we can do so using Query Analyzer?

We have tried Ceiling, but it doesn't work for us. It will always round up to a whole number, which is not what we want.

Thanks
0
Comment
Question by:mei_liu
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13579295
I'm not sure what you're asking for.  ??

Are you saying using CEILING with 0.5 and 0.4 rounds up to 1.0?

What results are you looking for exactly if CEILING doesn't give you what you want?
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 13579440
I am a little confused as well.  CEILING is supposed to round up to the next whole number.  Why aren't you using the ROUND() function?
0
 

Author Comment

by:mei_liu
ID: 13579469
This is what we are looking for:

If the invoice amount is $36.1225, then we need the result to be $36.13;
If the invoice amount if $36.1256, then we need the result to be $36.13;

We need the amount to always round up.

THanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 13579478
Aww that is simple, CEILING(x*100)/100.0
0
 

Author Comment

by:mei_liu
ID: 13579490
Thanks. When testing ceiling(X*100)/100.0, we get "Syntax error converting the varchar value '0.00' to a column of data type int". Could you please let us know why?
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 1000 total points
ID: 13579607
Oops, sorry, change it to CEILING(x*100.0)/100.0

By the way, X must be a numeric column.  If the column with X is text such as $36.1225 then you need to convert the string into a numeric format like so:

CEILING(CAST(REPLACE(REPLACE('$3,600.1234', '$', ''), ',', '') AS MONEY) * 100.0) / 100.0)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question