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

    number

    Required: Yes
  • digits

    Required: Yes
  • ROUNDUP

    number

    Required: Yes
  • digits

    Required: Yes
  • ROUNDDOWN

    number

    Required: Yes
  • digits

    Required: Yes
  • CEILING

    number

    Required: Yes
  • significance

    Required: Yes
  • FLOOR

    number

    Required: Yes
  • significance

    Required: Yes
  • MROUND

    number

    Required: Yes
  • multiple

    Required: Yes
  • TRUNC

    number

    Required: Yes
  • INT

    number

    Required: Yes
  • EVEN

    number

    Required: Yes
  • ODD

    number

    Required: Yes
  • CEILING.MATH

    number

    Required: Yes
  • significance

    Required: No
  • mode

    Required: No
  • CEILING.PRECISE

    number

    Required: Yes
  • significance

    Required: No
  • ISO.CEILING

    number

    Required: Yes
  • significance

    Required: No
  • FLOOR.MATH

    number

    Required: Yes
  • significance

    Required: No
  • mode

    Required: No
  • FLOOR.PRECISE

    number

    Required: Yes
  • significance

    Required: No

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.

FunctionReturnsBehaviourForm
ROUNDdecimalRounds to the nearest value at that placeROUND(2.345, 2)
ROUNDUPdecimalRounds away from zeroROUNDUP(2.341, 2)
ROUNDDOWNdecimalRounds towards zeroROUNDDOWN(2.349, 2)
CEILINGdecimalRounds up to the next multiple of the significanceCEILING(2.1, 1.0)
FLOORdecimalRounds down to the previous multiple of the significanceFLOOR(2.9, 1.0)
MROUNDdecimalRounds to the nearest multipleMROUND(10, 3)
TRUNCwhole numberDrops the part after the decimal pointTRUNC(2.987)
INTwhole numberRounds down to the next whole number below, so a negative value moves away from zeroINT(-2.5)
EVENwhole numberRounds away from zero to the next even whole numberEVEN(3)
ODDwhole numberRounds away from zero to the next odd whole numberODD(2)
CEILING.MATHdecimalRounds up to the next multiple of the significance. A negative significance is not made positiveCEILING.MATH(12.34, 0.5)
CEILING.PRECISEdecimalRounds up, towards the larger number, to the next multiple of the significanceCEILING.PRECISE(-12.34, 0.5)
ISO.CEILINGdecimalRounds up, towards the larger number, to the next multiple of the significance. A whole answer comes back as a whole numberISO.CEILING(12.34, 0.5)
FLOOR.MATHdecimalRounds down to the previous multiple of the significanceFLOOR.MATH(12.34, 0.5)
FLOOR.PRECISEdecimalRounds down, towards the smaller number, to the previous multiple of the significanceFLOOR.PRECISE(-12.34, 0.5)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2005 Conversion errorAn argument is not a number, such as ROUND(1.5, "a")Pass numbers
HTQL-2002CEILING was given a number and a multiple with different signs, or FLOOR a multiple of 0Give both the same sign, and a multiple that is not 0
HTQL-8004FLOOR was given a number and a multiple with different signs, such as FLOOR(-2.5, 1)Give both the same sign
HTQL-2007MROUND was given a number and a multiple with different signs, or a multiple of 0Give both the same sign, and a multiple that is not 0
HTQL-2001TRUNC was given a whole number, or MROUND or ODD was given textWrite TRUNC's value as a decimal such as 2.0
Limitations
  • ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR and MROUND take exactly two arguments. TRUNC takes one.
  • A value that is not a number is refused.
  • TRUNC accepts a decimal only: TRUNC(2) is refused with HTQL-2001, so write TRUNC(2.0).
  • CEILING, FLOOR and MROUND need the number and the multiple to have the same sign. CEILING answers 0 and FLOOR refuses a multiple of 0.
  • A result such as MROUND(19.97, 0.05) can carry binary rounding noise (19.950000000000003). Round it with ROUND(…, 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 digits to round to the nearest hundred or thousand.