TRUNCATE()

Syntax

How it works

The numeric TRUNCATE(number, decimals) function keeps a fixed number of decimal places and throws away the rest. Unlike ROUND(), it never rounds up: it simply cuts the number toward zero, so TRUNCATE(9.99, 1) is 9.9, not 10.0. This makes it useful when you must display or store a value at a fixed precision without inflating it, such as showing money to two decimal places without rounding a price up.

The second argument controls where the cut happens. A positive decimals keeps that many places after the point. Zero cuts to a whole number, similar to FLOOR() for positive numbers but differing for negatives, because truncation goes toward zero while FLOOR() goes toward negative infinity. A negative decimals zeroes out digits before the decimal point, so TRUNCATE(12345, -2) is 12300.

The function name is not the same across engines. MySQL uses TRUNCATE(n, d). PostgreSQL and Oracle use TRUNC(n, d). SQL Server has no dedicated truncate function, so you pass a third argument to ROUND(): ROUND(n, d, 1), where any non-zero third argument means truncate instead of round.

Not the same as TRUNCATE TABLE. This page is about the numeric function that shortens a number. TRUNCATE TABLE mytable is a completely separate DDL statement that deletes every row in a table. They share a keyword but do totally different things. See the note below.

Examples

Cut to two decimal places without rounding

-- 9.999 stays 9.99, it is NOT rounded to 10.00
SELECT TRUNCATE(9.999, 2) AS price;

price
-----
 9.99

Truncate to a whole number

SELECT TRUNCATE(15.87, 0) AS whole;

whole
-----
   15

Negative decimals zero out left of the point

-- keep hundreds, zero the last two digits
SELECT TRUNCATE(12345, -2) AS rounded_down;

rounded_down
------------
       12300

Truncation goes toward zero for negatives

-- toward zero, so -9.99 becomes -9.9, not -10.0
SELECT TRUNCATE(-9.99, 1) AS n;

n
----
-9.9

PostgreSQL and Oracle use TRUNC

-- same behaviour, different name
SELECT TRUNC(9.999, 2) AS price;

price
-----
 9.99

SQL Server uses ROUND with a third argument of 1

-- the 3rd argument 1 means truncate instead of round
SELECT ROUND(9.999, 2, 1) AS price;

price
-----
 9.99

Common mistakes

-- Deletes EVERY row in the table!
TRUNCATE TABLE orders;

-- The numeric function just shortens a number
SELECT TRUNCATE(order_total, 2) FROM orders;

-- Expecting this to round up to 10.00
SELECT TRUNCATE(9.999, 2);  -- gives 9.99

-- Use ROUND() when you actually want rounding
SELECT ROUND(9.999, 2);     -- gives 10.00

-- Fails on PostgreSQL and Oracle: no such function
SELECT TRUNCATE(9.999, 2);

-- PostgreSQL and Oracle spell it TRUNC
SELECT TRUNC(9.999, 2);

-- Assuming FLOOR and TRUNCATE are the same for negatives
SELECT FLOOR(-9.5);        -- gives -10

-- TRUNCATE goes toward zero, FLOOR toward -infinity
SELECT TRUNCATE(-9.5, 0);  -- gives -9

Performance

TRUNCATE() is a cheap, per-row scalar operation, so its own cost is negligible. The real performance concern is that wrapping a column in any function, including TRUNCATE(), inside a WHERE or JOIN condition prevents the optimiser from using an index on that column, forcing a full scan.

If you frequently filter or group by a truncated value, store or index the truncated result rather than computing it at query time. Many engines let you build an expression index or a computed column, such as a TRUNC(amount, 2) index in PostgreSQL, so the truncation is done once at write time instead of on every read.

Prefer truncating in the SELECT list for display, and keep raw values in the WHERE clause so indexes stay usable. See the indexing guide for why functions on indexed columns hurt.

Interview questions

SELECT ROUND(9.999, 2, 1) AS price;  -- 9.99