SQLite trunc() Function

Summary: in this tutorial, you will learn how to use the SQLite trunc() function to return the integer part of a number, rounding toward zero.

Introduction to the SQLite trunc() function

In SQLite, the trunc() function returns the integer part of a number, rounding toward zero.

Here’s the syntax of the trunc() function:

trunc(x)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• x is a number, an expression, or a table column to obtain the integer part.

The trunc() function returns the integer part of x, rounding toward zero. If x is NULL the trunc() function returns NULL.

The trunc() function works like the floor() or ceiling() functions except that it always rounds the input number toward zero whereas the floor() and ceiling() functions round the input number toward zero or away from zero depending on whether the number is positive or negative.

SQLite trunc() function examples

Let’s take some examples of using the trunc() function.

1) Using SQLite trunc() function with a positive number

The following example uses the trunc() function to return the integer part of the number 10.99, rounding toward zero:

SELECT trunc(10.99) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
10.0Code language: SQL (Structured Query Language) (sql)

2) Using the trunc() function with a negative number

The following example uses the trunc() function to return the integer part of the number –10.99, rounding toward zero:

SELECT trunc(-10.99) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
-10.0Code language: SQL (Structured Query Language) (sql)

3) Using the trunc() function with table data

We’ll use the trunc() function with the tracks table from the sample database.

The following query returns the average length of tracks in milliseconds:

SELECT avg(milliseconds)
FROM tracks;Code language: SQL (Structured Query Language) (sql)

Output:

avg(milliseconds)
-----------------
393599.2121039109Code language: SQL (Structured Query Language) (sql)

To get the average length of tracks in milliseconds without the decimal places, you can use the trunc() function as shown in the following query:

SELECT trunc(avg(milliseconds)) length
FROM tracks;Code language: SQL (Structured Query Language) (sql)

Output:

length
--------
393599.0Code language: SQL (Structured Query Language) (sql)

Summary

• Use the trunc() function to return the integer part of a number, rounding toward zero.