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
Medium Priority
332 Views
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
Question by:mei_liu
• 3
• 2

LVL 33

Expert Comment

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

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

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

LVL 51

Expert Comment

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

Author Comment

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

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

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
Course of the Month13 days, 2 hours left to enroll