Oracle: TRUNC(date) Function

By Atul Rai | Last Updated: August 13, 2023 Previous       Next

Oracle’s TRUNC(date) function is a useful tool for trimming or eliminating the time component from dates. The TRUNC(date) function allows you to concentrate just on the date portion of date values that contain both date and time components.

Syntax:

The basic syntax of the TRUNC(date)) function is as follows:

TRUNC(date_expression, format)

Here, date_expression is the date you want to truncate, and format (optional) specifies the unit to which you want to truncate the date.

Let’s explore some examples to see how the TRUNC(date) function works:

1. Truncate to the Day

Suppose you have a date value ‘2023-08-13 15:30:45‘ and you want to focus only on the day, ignoring the time. You can achieve this using the TRUNC(date) function:

SELECT 
  TRUNC(
    TO_DATE('2023-08-13 15:30:45', 'YYYY-MM-DD HH24:MI:SS'),'DD')
    AS truncated_date 
FROM 
  dual;

Output: ‘2023-08-13’

2. Truncate to the Month

If you’re interested in truncating to the month, consider the following example:

SELECT 
  TRUNC(
    TO_DATE('2023-08-13 15:30:45', 'YYYY-MM-DD HH24:MI:SS'),'MM')
    AS truncated_date 
FROM 
  dual;

Output: ‘2023-08-01’

Similar Posts:

• Oracle: DENSE_RANK() Function
• Oracle: RANK() vs. DENSE_RANK() Function

3. Truncate to the Year

To truncate a date to the year level, use this example:

SELECT 
  TRUNC(
    TO_DATE('2023-08-13 15:30:45', 'YYYY-MM-DD HH24:MI:SS'),'YYYY')
    AS truncated_date 
FROM 
  dual;

Output: ‘2023-01-01’

4. Truncate Without Specifying Format

If you don’t specify a format, the TRUNC(date) function defaults to truncating to the day:

SELECT 
  TRUNC(
    TO_DATE('2023-08-13 15:30:45', 'YYYY-MM-DD HH24:MI:SS'))
    AS truncated_date 
FROM 
  dual;

Output: ‘2023-08-13’

References

1. TRUNC(date) – Oracle
2. Oracle: RANK() Function