Always rounding up

Posted on 2005-03-18
Medium Priority
Last Modified: 2008-03-06

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.

Question by:mei_liu
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
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?

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.

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

LVL 51

Expert Comment

by:Ted Bouskill
ID: 13579478
Aww that is simple, CEILING(x*100)/100.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?
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)

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 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