Page 1 of 1

[Solved] RoundUp Function

PostPosted: Tue Sep 03, 2019 6:09 am
by gkick
Hi,

The Round Function in HSQL 2.5 rounds up or down to the left or right based on .5
How can I round any decimal value of a numeric field up to an integer, so that 45.3 turns into 46 etc?
Any possibility of sucking up CALCs roundup function?
see attached image, thanks

Re: RoundUp Function

PostPosted: Tue Sep 03, 2019 7:00 am
by Sliderule
  1. Please check out HSQL Built-In Functions:

    http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html


  2. Please see the documentation for the Numeric Functions:

    http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_numeric_functions

    http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_numeric_functions wrote:
    CEILING

    { CEIL | CEILING } ( <num value expr> )

    Returns the smallest integer greater than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (JDBC and Foundation)
Therefore, see examples below to answer your question.

Code: Select all   Expand viewCollapse view
CEILING(45.3)


Code: Select all   Expand viewCollapse view
CEILING("MY_TALBE"."MY_NUMERIC_COLUMN")


I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: RoundUp Function

PostPosted: Tue Sep 03, 2019 7:15 am
by gkick
Thanks a lot Sliderule, put the AGE_Float function to work as well

CEILING(AGE_FLOAT( [tblContacts].[DateOfBirth], CURRENT_DATE )) Would never have thought to look for ceiling

Re: RoundUp Function

PostPosted: Tue Sep 03, 2019 2:44 pm
by Sliderule
gkick wrote:Would never have thought to look for ceiling

That is exactly why I pointed you the HTML version of the HSQL database back-end documentation.

Likewise, you downloaded the PDF Version, as a part of HSQL download . . . in the doc -> guide directory, with file name guide.pdf.

For others reading this forum post . . . another similar function is FLOOR

http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_numeric_functions wrote:
FLOOR

FLOOR ( <num value expr> )

Returns the largest integer that is less than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (JDBC
and Foundation)

Sliderule