MySQL TRUNCATE Function: A Comprehensive Guide

Summary: This tutorial provides an in-depth explanation of the MySQL TRUNCATE() function, including how to use it to truncate numbers to specific decimal places, with detailed examples demonstrating various use cases.

Understanding the MySQL TRUNCATE() Function

The TRUNCATE() function is a built-in MySQL numeric function that allows you to truncate (cut off) a number to a specified number of decimal places. Unlike rounding functions that adjust the final digit based on the following digit, TRUNCATE() simply removes the unwanted decimal places without any rounding logic.

Key Concept: Truncation vs. Rounding

Truncation means cutting off digits after a certain point without considering their values. For example, truncating 3.14159 to 2 decimal places gives you 3.14, regardless of whether the next digit is 9 or 1.

Rounding, on the other hand, considers the value of the next digit. Rounding 3.14159 to 2 decimal places gives you 3.14, but rounding 3.14559 to 2 decimal places gives you 3.15.

Syntax

TRUNCATE(X, D)

Parameters:

• X (required): The number or numeric expression you want to truncate. This can be:
• A literal number (e.g., 123.456)
• A column reference containing numeric values
• A numeric expression or calculation
• D (required): The number of decimal places to keep. This parameter determines the precision of the result:
• Positive D: Keeps D decimal places to the right of the decimal point
• Zero D: Removes all decimal places, keeping only the integer part
• Negative D: Truncates D digits to the left of the decimal point, replacing them with zeros

Return Value:

The function returns a numeric value truncated to the specified number of decimal places.

Detailed Examples

Example 1: Truncating with Positive Decimal Places

When you use a positive value for D, the TRUNCATE() function preserves that many digits after the decimal point and discards the rest.

-- Example with various positive decimal places
SELECT
    TRUNCATE(123.456789, 0) AS 'zero_decimals',
    TRUNCATE(123.456789, 1) AS 'one_decimal',
    TRUNCATE(123.456789, 2) AS 'two_decimals',
    TRUNCATE(123.456789, 3) AS 'three_decimals',
    TRUNCATE(123.456789, 5) AS 'five_decimals';

Output:

Detailed Explanation:

• TRUNCATE(123.456789, 0) → 123: All decimal places are removed, leaving only the integer part
• TRUNCATE(123.456789, 1) → 123.4: Only the first decimal place (4) is kept; digits 56789 are discarded
• TRUNCATE(123.456789, 2) → 123.45: The first two decimal places (45) are kept; digits 6789 are discarded
• TRUNCATE(123.456789, 3) → 123.456: The first three decimal places (456) are kept; digits 789 are discarded
• TRUNCATE(123.456789, 5) → 123.45678: The first five decimal places are kept; only digit 9 is discarded

Practical Use Case: This is useful when displaying prices or measurements where you want to enforce a specific precision without rounding. For example, gas prices often need to be truncated to 3 decimal places (e.g., $3.459 per gallon).

Example 2: Truncating with Negative Decimal Places

When you use a negative value for D, the TRUNCATE() function sets that many digits to the left of the decimal point to zero. This effectively "rounds down" to the nearest power of 10.

-- Example with various negative decimal places
SELECT
    TRUNCATE(9876.54, -1) AS 'tens_place',
    TRUNCATE(9876.54, -2) AS 'hundreds_place',
    TRUNCATE(9876.54, -3) AS 'thousands_place',
    TRUNCATE(9876.54, -4) AS 'ten_thousands_place';

Output:

Detailed Explanation:

• TRUNCATE(9876.54, -1) → 9870: The ones digit (6) is set to zero. The decimal part is also removed.
• TRUNCATE(9876.54, -2) → 9800: The ones (6) and tens (7) digits are set to zero. We keep 98 in the hundreds position.
• TRUNCATE(9876.54, -3) → 9000: The ones (6), tens (7), and hundreds (8) digits are set to zero. We keep 9 in the thousands position.
• TRUNCATE(9876.54, -4) → 0: All digits are set to zero because we're truncating to the ten-thousands place, and our number is less than 10,000.

How It Works:

Think of negative decimal places as moving from right to left through the digits:

• Original number: 9876.54
• D = -1: Set the 1s place to 0 → 9870
• D = -2: Set the 10s and 1s places to 0 → 9800
• D = -3: Set the 100s, 10s, and 1s places to 0 → 9000

Practical Use Case: This is helpful for statistical data aggregation, budget reporting, or when you need to group numbers into ranges. For example, truncating salaries to the nearest thousand for salary band analysis.

Example 3: TRUNCATE() vs. ROUND() - Understanding the Critical Difference

This example demonstrates the fundamental difference between truncation and rounding with various test cases.

-- Comparing TRUNCATE() and ROUND() with different scenarios
SELECT
    'Case 1: 1.999' AS scenario,
    TRUNCATE(1.999, 1) AS truncate_result,
    ROUND(1.999, 1) AS round_result

UNION ALL

SELECT
    'Case 2: 1.999' AS scenario,
    TRUNCATE(1.999, 2) AS truncate_result,
    ROUND(1.999, 2) AS round_result

UNION ALL

SELECT
    'Case 3: 8.555' AS scenario,
    TRUNCATE(8.555, 1) AS truncate_result,
    ROUND(8.555, 1) AS round_result

UNION ALL

SELECT
    'Case 4: 199.99' AS scenario,
    TRUNCATE(199.99, -2) AS truncate_result,
    ROUND(199.99, -2) AS round_result

UNION ALL

SELECT
    'Case 5: -3.789' AS scenario,
    TRUNCATE(-3.789, 2) AS truncate_result,
    ROUND(-3.789, 2) AS round_result;

Output:

Detailed Explanation:

Case 1: 1.999 to 1 decimal place

• TRUNCATE: Simply cuts off after the first decimal → 1.9
• ROUND: Looks at the second decimal (9) and rounds up → 2.0
• Difference: 0.1

Case 2: 1.999 to 2 decimal places

• TRUNCATE: Cuts off after the second decimal → 1.99
• ROUND: Looks at the third decimal (9) and rounds up → 2.00
• Difference: 0.01

Case 3: 8.555 to 1 decimal place

• TRUNCATE: Cuts off after the first decimal → 8.5
• ROUND: Looks at the second decimal (5) and rounds up → 8.6
• Difference: 0.1

Case 4: 199.99 with -2 (negative decimal places)

• TRUNCATE: Sets the tens and ones digits to zero → 100
• ROUND: Rounds to the nearest hundred → 200
• Difference: 100 (substantial difference!)

Case 5: -3.789 to 2 decimal places (negative numbers)

• TRUNCATE: Cuts off the last digit → -3.78 (moves toward zero)
• ROUND: Rounds based on the third decimal (9) → -3.79 (moves away from zero)
• Note: For negative numbers, truncation moves toward zero, while rounding follows standard rounding rules

Key Takeaway:

• Use TRUNCATE() when you need strict cutoff without adjustment (e.g., discount calculations, tax truncation, display formatting)
• Use ROUND() when you need mathematically accurate rounding (e.g., financial calculations, scientific measurements)

Real-World Applications

1. Financial Calculations

-- Truncating transaction fees to 2 decimal places
SELECT
    transaction_id,
    amount,
    TRUNCATE(amount * 0.029, 2) AS processing_fee
FROM transactions;

2. Inventory Management

-- Truncating weights to nearest 10 grams (0.01 kg)
SELECT
    product_name,
    weight_kg,
    TRUNCATE(weight_kg, 2) AS displayed_weight
FROM products;

3. Statistical Reporting

-- Grouping salaries into $10,000 bands
SELECT
    TRUNCATE(salary, -4) AS salary_band,
    COUNT(*) AS employee_count
FROM employees
GROUP BY salary_band;

Important Notes

1. Both Parameters Are Required: Unlike some functions, TRUNCATE() requires both X and D parameters. Omitting either will result in an error.
2. No Automatic Rounding: TRUNCATE() never rounds values up or down—it strictly removes digits.
3. Return Type: The function returns a numeric type (typically DECIMAL) that preserves the requested precision.
4. Negative Numbers: When truncating negative numbers, truncation always moves toward zero (e.g., TRUNCATE(-1.9, 0) returns -1, not -2).
5. Performance: TRUNCATE() is a lightweight function with minimal performance overhead, making it suitable for use on large datasets.

Summary

The MySQL TRUNCATE() function is a powerful tool for controlling numeric precision by cutting off unwanted decimal places. Key points to remember:

• Syntax: TRUNCATE(X, D) where X is the number and D is the decimal places
• Positive D: Keeps D decimal places to the right of the decimal point
• Negative D: Sets D digits to the left of the decimal point to zero
• No Rounding: Unlike ROUND(), TRUNCATE() simply cuts off digits without any rounding logic
• Use Cases: Display formatting, financial truncation, statistical grouping, and precision control

By understanding the distinction between truncation and rounding, you can choose the appropriate function for your specific use case and ensure accurate data processing in your MySQL applications.