Skip to main content

trunc

1. TRUNC Function​

The TRUNC function in H2 database is used to truncate a numeric value to a specified number of decimal places.

2. Syntax​

The syntax for the TRUNC function in H2 database is as follows:

TRUNC(value, decimal_places)

Arguments​

  • value: The numeric value that needs to be truncated.
  • decimal_places: The number of decimal places to which the value should be truncated. It should be an integer value.

Return​

  • The TRUNC function returns the truncated value.

3. Notes​

  • The TRUNC function in H2 database truncates the value towards zero, meaning it removes the decimal places without rounding.
  • If the input value is not a number or exceeds the numeric range, the TRUNC function will return NULL.
  • Make sure to provide the correct number of decimal places in order to achieve the desired truncation.

4. Examples​

Here are a few examples demonstrating the usage of the TRUNC function in H2 database:

Example 1 - Truncating a value to 2 decimal places:​

SELECT TRUNC(3.14159, 2) AS truncated_value;

Output:

truncated_value
---------------
3.14

Example 2 - Truncating a column's values to 0 decimal places:​

CREATE TABLE prices (
  id INT PRIMARY KEY,
  price DECIMAL(8, 2)
);

INSERT INTO prices VALUES (1, 19.99), (2, 45.678), (3, 99.95);

SELECT id, price, TRUNC(price, 0) AS truncated_price
FROM prices;

Output:

id | price  | truncated_price
---+--------+----------------
1  | 19.99  | 19
2  | 45.678 | 45
3  | 99.95  | 99
  • round - Round to a specified number of decimal places
  • CEILING - Round up to the nearest integer
  • floor - Round down to the nearest integer