Oracle Plsql Trunc Function (with Dates)
Oracle / PLSQL: TRUNC Function (with dates)
This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function (as it applies to date values) with syntax and examples.
Description
The Oracle/PLSQL TRUNC function returns a date truncated to a specific unit of measure.
Syntax (with dates)
The syntax for the TRUNC function in Oracle/PLSQL is:
TRUNC ( date [, format ] )Parameters or Arguments
- date
- format
The date to truncate.
Optional. The unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function 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 format 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 function (as it applies to dates) returns a date value.
Applies To
The TRUNC function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example - with dates
Let's look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.
For example:
TRUNC(TO_DATE('22-AUG-03'), 'YEAR')
Output: '01-JAN-03'
TRUNC(TO_DATE('22-AUG-03'), 'Q')
Output: '01-JUL-03'
TRUNC(TO_DATE('22-AUG-03'), 'MONTH')
Output: '01-AUG-03'
TRUNC(TO_DATE('22-AUG-03'), 'DDD')
Output: '22-AUG-03'
TRUNC(TO_DATE('22-AUG-03'), 'DAY')
Output: '17-AUG-03'