TRUNC Date

The TRUNC function in Oracle is used to truncate a date or timestamp to a specified unit of time, effectively "removing" or zeroing out the smaller time components. This is particularly useful when you want to normalize dates by removing the time portion or truncating to a specific date part (e.g., month, year, etc.).

 

Syntax:

TRUNC(date_value, format)

• date_value: The date or timestamp that you want to truncate.
• format (optional): The unit to which the date should be truncated (e.g., YEAR, MONTH, DAY, HH, MI, etc.).

If the format is omitted, the function defaults to truncating the time part, so the result is a date with the time set to midnight (00:00:00).

 

Return Type:

• The return type is a date value that has been truncated based on the specified unit. If the time part is truncated, it will default to 00:00:00 (midnight).

 

Common format Values:

The format argument specifies to which part of the date you want to truncate. Some common values include:

• 'YEAR': Truncates to the first day of the year (i.e., sets the date to January 1st of that year).
• 'MONTH': Truncates to the first day of the month (i.e., sets the date to the 1st day of the month).
• 'DAY': Truncates to the start of the day (i.e., sets the time to midnight 00:00:00).
• 'HH' (or 'HH24', 'HH12'): Truncates to the start of the hour (i.e., sets the minute and second to zero).
• 'MI': Truncates to the start of the minute (i.e., sets the second to zero).
• 'SS': Truncates to the start of the second (i.e., keeps the second and removes any fractional seconds).
• 'Q': Truncates to the first day of the quarter (1st, 2nd, 3rd, or 4th quarter of the year).

 

Examples of Using TRUNC:

1. Truncate to the Nearest Year:

This truncates the date to the first day of the specified year.

SELECT TRUNC(DATE '2025-08-15', 'YEAR') FROM dual;

-- Result: 01-JAN-2025

Explanation: The date 2025-08-15 is truncated to 01-JAN-2025, the first day of the year.

2. Truncate to the Nearest Month:

This truncates the date to the first day of the specified month.

SELECT TRUNC(DATE '2025-08-15', 'MONTH') FROM dual;

-- Result: 01-AUG-2025

Explanation: The date 2025-08-15 is truncated to 01-AUG-2025, the first day of the month.

3. Truncate to the Nearest Day:

This truncates the date to the start of the day (midnight).

SELECT TRUNC(DATE '2025-08-15 13:45:30', 'DAY') FROM dual;

-- Result: 15-AUG-2025 00:00:00

Explanation: The date 2025-08-15 13:45:30 is truncated to 15-AUG-2025 00:00:00.

4. Truncate to the Nearest Hour:

This truncates the timestamp to the beginning of the hour (minute and second set to zero).

SELECT TRUNC(TIMESTAMP '2025-08-15 13:45:30', 'HH') FROM dual;

-- Result: 15-AUG-2025 13:00:00

Explanation: The timestamp 2025-08-15 13:45:30 is truncated to 15-AUG-2025 13:00:00.

5. Truncate to the Nearest Minute:

This truncates the timestamp to the start of the minute (second set to zero).

SELECT TRUNC(TIMESTAMP '2025-08-15 13:45:30', 'MI') FROM dual;

-- Result: 15-AUG-2025 13:45:00

Explanation: The timestamp 2025-08-15 13:45:30 is truncated to 15-AUG-2025 13:45:00.

6. Truncate to the Nearest Quarter:

This truncates the date to the first day of the quarter.

SELECT TRUNC(DATE '2025-08-15', 'Q') FROM dual;

-- Result: 01-JUL-2025

Explanation: The date 2025-08-15 is truncated to 01-JUL-2025, which is the start of the third quarter.

 

Additional Notes:

1.     Rounding vs Truncating:

• Unlike ROUND, which rounds values to the nearest specified unit, TRUNC always removes the smaller components of the date without rounding. For instance, truncating 2025-08-15 13:45:30 to the DAY will return 2025-08-15 00:00:00, and truncating 2025-08-15 13:45:30 to HH will return 2025-08-15 13:00:00.

2.     Default Behavior:

• If you do not specify a format, TRUNC defaults to truncating the time to midnight, meaning the time component is removed entirely, and the date is preserved.

3.     Time Zones:

• When truncating TIMESTAMP WITH TIME ZONE values, the time zone portion of the timestamp remains unchanged. Only the date and time parts are truncated.

4.     Null Handling:

• If the date_value is NULL, the TRUNC function will return NULL.

5.     Performance Consideration:

• Using TRUNC on a DATE or TIMESTAMP column in a query (especially in WHERE clauses) may prevent the database from using indexes on the column, leading to performance degradation. In such cases, it's advisable to apply TRUNC only when necessary or to consider storing the truncated values in a separate column.

6.     Truncating Different Intervals:

• You can use TRUNC to truncate to other intervals such as weeks, hours, minutes, or even seconds, depending on the use case. For instance, truncating to the week might give you the start of the week (e.g., Sunday).

7.     Multiple Units of Truncation:

• You can combine TRUNC with other functions like SYSDATE to truncate the current date to different units:

8.  SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;

9.  -- Result: 01-JAN-2025 (if current month is January)

 

Use Cases:

1.     Normalizing Dates:

• When you need to group or aggregate data by specific intervals (such as month, year, or quarter), truncating dates to those intervals helps normalize the data.

2.     Business Reporting:

• In financial or business reporting, you might need to truncate dates to the beginning of a fiscal year or quarter to generate reports.

3.     Removing Time from Dates:

• When working with date comparisons, truncating the time part ensures that only the date is compared, which is helpful for tasks like finding records for a specific day regardless of the time.

4.     Standardizing Dates for Grouping:

• In time-series data, truncating dates to specific intervals like hours, days, or months can help in standardizing data points for analysis or visualization.

 

Summary:

The TRUNC function in Oracle is used to truncate dates or timestamps to a specified unit of time, such as year, month, day, hour, minute, or even quarter. It is particularly useful for normalizing dates, simplifying comparisons, and ensuring consistent intervals in reporting. By understanding how the function works and the different available formats, you can effectively manage date-related data in your Oracle applications.