Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
TRUNC
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The TRUNC()
function rounds a numeric value to its nearest integer (or optionally, to a specific decimal precision) that is closer to zero.
SELECT trunc(1.7), trunc(-1.7);
create.select( trunc(1.7), trunc(-1.7)).fetch();
The result being
+-------+-------+ | trunc | trunc | +-------+-------+ | 1 | -1 | +-------+-------+
Dialect support
This example using jOOQ:
trunc(1.7)
Translates to the following dialect specific expressions:
ASE
CASE WHEN sign(1.7E0) >= 0 THEN (floor((1.7E0 * 1)) / 1) ELSE (ceiling((1.7E0 * 1)) / 1) END
Aurora MySQL, MariaDB, MemSQL, MySQL
truncate(1.7E0, 0)
Aurora Postgres, Postgres
CAST(trunc( CAST(1.7E0 AS numeric), 0 ) AS double precision)
DB2, Firebird, HSQLDB, Informix, Oracle, Teradata, Vertica
trunc(1.7E0, 0)
Derby
CASE WHEN sign(1.7E0) >= 0 THEN (floor((1.7E0 * 1)) / 1) ELSE (ceil((1.7E0 * 1)) / 1) END
H2
truncate(CAST(1.7E0 AS double), 0)
Hana
round(1.7E0, 0, round_down)
SQLDataWarehouse, SQLServer
round(1.7E0, 0, 1)
Sybase
truncnum(1.7E0, 0)
Access, BigQuery, ClickHouse, CockroachDB, DuckDB, Exasol, Redshift, SQLite, Snowflake, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!