MySQL TRUNCATE Function

The TRUNCATE() function in MySQL is used to remove the decimal places from a number to a specified precision. It does not round the number, but simply cuts off the digits.

1. What is TRUNCATE() in MySQL?

✅ Removes decimal places from a number
✅ Does not round the number (just cuts off digits)
✅ Useful for precision control in financial or statistical data

🔹 Syntax:

TRUNCATE(number, decimal_places)

number: The value to be truncated
decimal_places: The number of decimal places to keep

2. MySQL TRUNCATE() Function Examples

Example 1: Basic Usage

SELECT TRUNCATE(123.4567, 2) AS result;

🔹 Output:

result
123.45

✅ Truncates the number to 2 decimal places.

Example 2: Truncate to an Integer

SELECT TRUNCATE(123.999, 0) AS result;

🔹 Output:

result
123

✅ Cuts off decimals without rounding.

Example 3: Using a Negative Decimal Place

SELECT TRUNCATE(12345, -2) AS result;

🔹 Output:

result
12300

✅ Removes digits before the decimal point, replacing them with 0.

3. Difference Between TRUNCATE() and ROUND()

Function	Effect
TRUNCATE(123.789, 2)	✅ 123.78 (cuts off digits)
ROUND(123.789, 2)	❌ 123.79 (rounds up)

🚀 Use TRUNCATE() when you only want to cut off digits, not round values.

4. When to Use TRUNCATE()?

✅ Financial calculations (e.g., removing extra decimal places)
✅ Data precision control (e.g., limiting values in reports)
✅ Avoiding rounding errors when exact values matter

5. Conclusion

TRUNCATE() removes decimal places without rounding.
It can also be used with negative values to truncate whole numbers.
Use it instead of ROUND() when rounding is not required.

🚀 Use TRUNCATE() for precise control over number formatting in MySQL!

Souy Soeng

MySQL TRUNCATE Function