Oracle / PLSQL: TRUNC Function (with dates)

This article is written about how to use the Oracle/PLSQL TRUNC feature (as it applies to date values) with syntax and examples.

Description

The Oracle/PLSQL TRUNC characteristic returns a date truncated to a specific unit of measure.

Syntax (with dates)

The syntax for the TRUNC feature in Oracle/PLSQL is:

TRUNC ( date [, format ] )

Parameters or Arguments

date The date to truncate. format Optional. The unit of measure to observe for truncating. If the layout parameter is omitted, the TRUNC characteristic will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off. It can be one of the following values: Unit Valid layout parameters Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y ISO Year IYYY, IY, I Quarter Q Month MONTH, MON, MM, RM Week WW IW IW W W Day DDD, DD, J Start day of the week DAY, DY, D Hour HH, HH12, HH24 Minute MI

Returns

The TRUNC feature (as it applies to dates) returns a date value.

Applies To

The TRUNC function can be used in the following variations of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example – with dates

Let’s appear at some Oracle TRUNC function examples and explore how to use the TRUNC characteristic in Oracle/PLSQL.

For example:

TRUNC(TO_DATE('22-AUG-03'), 'YEAR')
Result: '01-JAN-03'

TRUNC(TO_DATE('22-AUG-03'), 'Q')
Result: '01-JUL-03'

TRUNC(TO_DATE('22-AUG-03'), 'MONTH')
Result: '01-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DDD')
Result: '22-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DAY')
Result: '17-AUG-03'