SQL TRUNC: Dropping the Fractional Part Toward Zero Without Rounding

TRUNC drops the fractional part of a number without rounding: everything after the point is simply chopped off. Unlike ROUND, the direction is always the same — toward zero — so the result is predictable for both positive and negative values.

What TRUNC does

In its simplest form TRUNC(x) returns the integer part of a number, discarding the fraction. There is no round-to-nearest: 3.99 becomes 3, not 4.

SELECT
  TRUNC(3.99)   AS a,   -- 3
  TRUNC(3.01)   AS b,   -- 3
  TRUNC(-3.99)  AS c,   -- -3
  TRUNC(2.5)    AS d;   -- 2

Key properties:

• Truncation always goes toward zero, not down: -3.99 gives -3, because -3 is closer to zero than -4.
• ROUND(2.5) would give 3, but TRUNC(2.5) gives 2: TRUNC never looks at the digits it drops.
• The result type is numeric, so precision is preserved.

TRUNC(x, n): cutting to a number of decimals

The second argument sets how many digits after the decimal point to keep. The rest is chopped off without rounding.

SELECT
  TRUNC(123.4567, 2)  AS two_dp,    -- 123.45
  TRUNC(123.4567, 0)  AS zero_dp,   -- 123
  TRUNC(123.4567, -1) AS neg_one;   -- 120

A negative n truncates to the left of the point: -1 zeroes the units, -2 the tens. Apply it to the employees table to bucket salaries into coarse hundreds without rounding up:

SELECT
  name,
  salary,
  TRUNC(salary, -2) AS salary_bucket
FROM employees
ORDER BY salary DESC;

An employee earning 4999 lands in the 4900 bucket, not 5000 — truncation never nudges a value up to the next place.

TRUNC vs FLOOR on negative numbers

For positive numbers TRUNC and FLOOR look identical. The divergence starts on negatives: FLOOR rounds down (toward minus infinity), while TRUNC rounds toward zero.

SELECT
  FLOOR(-3.2) AS floor_neg,  -- -4
  TRUNC(-3.2) AS trunc_neg,  -- -3
  FLOOR(3.2)  AS floor_pos,  --  3
  TRUNC(3.2)  AS trunc_pos;  --  3

This is easy to turn into a bug. Say orders stores refunds with a negative amount and you want the "whole dollars":

SELECT
  id,
  amount,
  FLOOR(amount) AS whole_floor,  -- -16 for -15.30
  TRUNC(amount) AS whole_trunc   -- -15 for -15.30
FROM orders
WHERE amount < 0;

Gotcha: if you want a symmetric "drop the fraction", use TRUNC. FLOOR on refunds overstates the charged-off amount by a whole dollar.

Truncating money vs rounding

Money is its own minefield. Accounting usually wants rounding (ROUND), but some calculations — fees, interest accrual, splitting a price — are required by law or contract to truncate, so you never "gift" the customer an extra cent.

SELECT
  u.id,
  SUM(o.amount)                         AS gross,
  ROUND(SUM(o.amount) * 0.029, 2)       AS fee_rounded,   -- rounding
  TRUNC(SUM(o.amount) * 0.029, 2)       AS fee_truncated  -- truncation
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

Remember:

• ROUND(2.575, 2) may give 2.58, while TRUNC(2.575, 2) is always 2.57.
• Truncation systematically understates the result; rounding does not. Choose deliberately.
• Compute on numeric, not float: TRUNC of a binary float can surprise you because of representation error.

MySQL: the function is called TRUNCATE

MySQL has no TRUNC function — its role is played by TRUNCATE, and there the second argument is mandatory.

-- MySQL
SELECT
  TRUNCATE(123.4567, 2) AS two_dp,   -- 123.45
  TRUNCATE(123.4567, 0) AS zero_dp;  -- 123

Do not confuse it with the TRUNCATE TABLE statement, which instantly empties a table — that is a different operation entirely. ClickHouse offers both trunc(x) and truncate(x, n) as synonyms. Remember one thing: TRUNC means "chop toward zero without rounding", and the function name and whether the second argument is required depend on the engine.