TRUNC Number

The TRUNC function in Oracle is used to truncate a numeric value to a specified number of decimal places or to the left of the decimal point. It removes the digits after the decimal point without rounding, unlike the ROUND function, which rounds the number to the nearest value.

 

1. Syntax of the TRUNC Function

The basic syntax of the TRUNC function is:

TRUNC(number, decimal_places)

• number: The numeric value that you want to truncate.
• decimal_places: The number of decimal places to truncate to. If this argument is omitted, Oracle truncates the number to the nearest integer (i.e., 0 decimal places).

 

2. How TRUNC Works

• Truncation means that the function removes the digits after the decimal point without rounding.
• If decimal_places is positive, it truncates the number to that number of decimal places.
• If decimal_places is negative, it truncates the number to the left of the decimal point (i.e., to tens, hundreds, etc.).

 

3. Examples of Using TRUNC

a. Truncating to 2 Decimal Places

SELECT TRUNC(123.4567, 2) FROM dual;

-- Output: 123.45

In this example, the number 123.4567 is truncated to two decimal places, resulting in 123.45.

b. Truncating to 0 Decimal Places (Integer)

SELECT TRUNC(123.4567) FROM dual;

-- Output: 123

Here, truncation occurs at the integer level, removing the decimal part and resulting in 123.

c. Truncating to Negative Decimal Places (Left of Decimal)

SELECT TRUNC(12345, -2) FROM dual;

-- Output: 12300

In this example, the number 12345 is truncated to the nearest hundred, resulting in 12300.

d. Truncating Negative Numbers

SELECT TRUNC(-123.4567, 2) FROM dual;

-- Output: -123.45

The TRUNC function also works with negative numbers. The number -123.4567 is truncated to two decimal places, resulting in -123.45.

e. Truncating to Negative Tens, Hundreds, Thousands

SELECT TRUNC(12345, -3) FROM dual;

-- Output: 12000

This truncates the number 12345 to the nearest thousand, resulting in 12000.

 

4. How Does TRUNC Differ from ROUND?

• TRUNC: Removes digits after the specified decimal place or to the left of the decimal point without rounding.
• ROUND: Rounds the number to the nearest value based on standard rounding rules.

Example:

SELECT ROUND(123.4567, 2) FROM dual;  -- Output: 123.46

SELECT TRUNC(123.4567, 2) FROM dual;  -- Output: 123.45

• ROUND rounds 123.4567 to 123.46, while TRUNC removes the extra decimals and returns 123.45.

 

5. Use Cases of the TRUNC Function

·        Handling precision: The TRUNC function is useful when you need to eliminate unwanted decimal places but don’t want to round the number.

·        Data formatting: You can use TRUNC to truncate numbers for formatting purposes, like displaying prices without rounding them.

·        Financial calculations: It is common to truncate numbers in financial or accounting calculations where rounding is not desired.

·        Truncating dates: You can also use TRUNC with dates to truncate them to the nearest day, month, or year.

 

6. TRUNC with Dates

The TRUNC function can be used to truncate date values, similar to how it works with numeric values.

• Truncating to the nearest day: Truncates a date to midnight of the same day.

SELECT TRUNC(SYSDATE) FROM dual;

-- Output: Date truncated to midnight (e.g., 27-FEB-2025 00:00:00)

• Truncating to the nearest month: Truncates a date to the first day of the month.

SELECT TRUNC(SYSDATE, 'MM') FROM dual;

-- Output: Date truncated to the first day of the current month (e.g., 01-FEB-2025)

• Truncating to the nearest year: Truncates a date to the first day of the year.

SELECT TRUNC(SYSDATE, 'YYYY') FROM dual;

-- Output: Date truncated to the first day of the current year (e.g., 01-JAN-2025)

 

7. Key Considerations with the TRUNC Function

·        Negative values: The TRUNC function can handle negative numbers, truncating them just like positive numbers.

·        Default behavior: If no decimal_places parameter is specified, TRUNC truncates the number to 0 decimal places (i.e., to an integer).

·        Subquery use: You can use TRUNC in subqueries to format data before using it in the main query.

 

8. Performance Considerations

The TRUNC function is efficient for handling numeric truncation in SQL queries. It can be used in queries involving large datasets, but care should be taken when applying it to large numbers of rows, especially if it's used in a JOIN or in combination with multiple functions.

For date truncation (e.g., truncating to the nearest day, month, or year), Oracle's internal date handling is optimized, so TRUNC on dates should not significantly impact performance.