The SQLite TRUNC() function is used to truncate a number to an integer. It returns the integer part of its argument rounding toward zero.

TRUNC()is similar to CEIL() and FLOOR(), except that TRUNC() always rounds toward zero. CEIL() on the other hand, always rounds up, and FLOOR() always rounds down.

Syntax

TRUNC(value)

Where value is the numeric value to truncate. Can be an integer or floating-point number.

Unlike some other database systems (such as PostgreSQL), SQLite’s implementation of TRUNC() does not accept a second argument for specifying decimal places. At least, that’s the case at the time of this writing.

Examples

Here’s a simple example to demonstrate:

SELECT TRUNC( 17.94 );

Output:

+----------------+
| TRUNC( 17.94 ) |
+----------------+
| 17.0           |
+----------------+

Some more:

SELECT 
       TRUNC(1.9),
       TRUNC(0.9),
       TRUNC(-1.9),
       TRUNC(-0.9),
       TRUNC(3),
       TRUNC(-3);

Output:

+------------+------------+-------------+-------------+----------+-----------+
| TRUNC(1.9) | TRUNC(0.9) | TRUNC(-1.9) | TRUNC(-0.9) | TRUNC(3) | TRUNC(-3) |
+------------+------------+-------------+-------------+----------+-----------+
| 1.0        | 0.0        | -1.0        | 0.0         | 3        | -3        |
+------------+------------+-------------+-------------+----------+-----------+

Passing Null Values

Passing null results in null:

SELECT TRUNC(null);

Output:

+-------------+
| TRUNC(null) |
+-------------+
| null        |
+-------------+

Passing the Wrong Data Type

Passing the wrong data type results in null:

SELECT TRUNC('two');

Output:

+--------------+
| TRUNC('two') |
+--------------+
| null         |
+--------------+