Rounding functions
Round when the rounded number is the business answer: the full cartons to order, a price on a five cent step, a target set to the nearest thousand.
Syntax
Write the function's name and the number, with the places or multiple the function asks for.
ROUND(number, digits)
ROUNDUP(number, digits)
ROUNDDOWN(number, digits)
CEILING(number, significance)
FLOOR(number, significance)
MROUND(number, multiple)
TRUNC(number)
INT(number)
EVEN(number)
ODD(number)
CEILING.MATH(number, [significance], [mode])
CEILING.PRECISE(number, [significance])
ISO.CEILING(number, [significance])
FLOOR.MATH(number, [significance], [mode])
FLOOR.PRECISE(number, [significance])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
ROUND
Required: Yesnumber
Required: YesdigitsROUNDUP
Required: Yesnumber
Required: YesdigitsROUNDDOWN
Required: Yesnumber
Required: YesdigitsCEILING
Required: Yesnumber
Required: YessignificanceFLOOR
Required: Yesnumber
Required: YessignificanceMROUND
Required: Yesnumber
Required: YesmultipleTRUNC
Required: YesnumberINT
Required: YesnumberEVEN
Required: YesnumberODD
Required: YesnumberCEILING.MATH
Required: Yesnumber
Required: Nosignificance
Required: NomodeCEILING.PRECISE
Required: Yesnumber
Required: NosignificanceISO.CEILING
Required: Yesnumber
Required: NosignificanceFLOOR.MATH
Required: Yesnumber
Required: Nosignificance
Required: NomodeFLOOR.PRECISE
Required: Yesnumber
Required: Nosignificance
Examples
The same kind of figure rounded to the nearest cent, always up, and always down, with the rule named in each function.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
ROUND | decimal | Rounds to the nearest value at that place | ROUND(2.345, 2) |
ROUNDUP | decimal | Rounds away from zero | ROUNDUP(2.341, 2) |
ROUNDDOWN | decimal | Rounds towards zero | ROUNDDOWN(2.349, 2) |
CEILING | decimal | Rounds up to the next multiple of the significance | CEILING(2.1, 1.0) |
FLOOR | decimal | Rounds down to the previous multiple of the significance | FLOOR(2.9, 1.0) |
MROUND | decimal | Rounds to the nearest multiple | MROUND(10, 3) |
TRUNC | whole number | Drops the part after the decimal point | TRUNC(2.987) |
INT | whole number | Rounds down to the next whole number below, so a negative value moves away from zero | INT(-2.5) |
EVEN | whole number | Rounds away from zero to the next even whole number | EVEN(3) |
ODD | whole number | Rounds away from zero to the next odd whole number | ODD(2) |
CEILING.MATH | decimal | Rounds up to the next multiple of the significance. A negative significance is not made positive | CEILING.MATH(12.34, 0.5) |
CEILING.PRECISE | decimal | Rounds up, towards the larger number, to the next multiple of the significance | CEILING.PRECISE(-12.34, 0.5) |
ISO.CEILING | decimal | Rounds up, towards the larger number, to the next multiple of the significance. A whole answer comes back as a whole number | ISO.CEILING(12.34, 0.5) |
FLOOR.MATH | decimal | Rounds down to the previous multiple of the significance | FLOOR.MATH(12.34, 0.5) |
FLOOR.PRECISE | decimal | Rounds down, towards the smaller number, to the previous multiple of the significance | FLOOR.PRECISE(-12.34, 0.5) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2005 Conversion error | An argument is not a number, such as ROUND(1.5, "a") | Pass numbers |
HTQL-2002 | CEILING was given a number and a multiple with different signs, or FLOOR a multiple of 0 | Give both the same sign, and a multiple that is not 0 |
HTQL-8004 | FLOOR was given a number and a multiple with different signs, such as FLOOR(-2.5, 1) | Give both the same sign |
HTQL-2007 | MROUND was given a number and a multiple with different signs, or a multiple of 0 | Give both the same sign, and a multiple that is not 0 |
HTQL-2001 | TRUNC was given a whole number, or MROUND or ODD was given text | Write TRUNC's value as a decimal such as 2.0 |
Limitations
ROUND,ROUNDUP,ROUNDDOWN,CEILING,FLOORandMROUNDtake exactly two arguments.TRUNCtakes one.- A value that is not a number is refused.
TRUNCaccepts a decimal only:TRUNC(2)is refused withHTQL-2001, so writeTRUNC(2.0).CEILING,FLOORandMROUNDneed the number and the multiple to have the same sign.CEILINGanswers 0 andFLOORrefuses a multiple of 0.- A result such as
MROUND(19.97, 0.05)can carry binary rounding noise (19.950000000000003). Round it withROUND(…, 2)before it is shown.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
Warning
Never round to make an answer look tidy. HTQLDB returns the full value, converts money and percentages in the answer, and the screen that shows it formats it. A rounded answer loses the precision every later calculation needs.
- Round once, at the end of a calculation, so rounding does not build up.
- Use ROUNDDOWN or ROUNDUP when the rule is not nearest.
- Use a negative
digitsto round to the nearest hundred or thousand.