The Oracle TRUNC()
function returns a DATE
value truncated to a specified unit.
Syntax
The following shows the syntax of the Oracle TRUNC()
function:
TRUNC(date, format)
Arguments
The TRUNC()
function accepts two arguments:
1) date
The date
argument is a DATE
value or an expression that evaluates to a DATE
value that will be truncated.
2) format
The format
argument determines the unit to which the date
will be truncated.
The format
argument is optional. Its default value is DD
that instructs the TRUNC()
function to truncate the date to midnight.
The following table illustrates valid values for the format
argument:
Format | Description |
---|---|
CC, SCC | Century, with or without minus sign (BC) |
[S]YYYY, [S]YEAR, YYY, YY, Y | Year (in various appearances) |
IYYY, IYY, IY, I | ISO year |
Q | Quarter |
MONTH, MON, MM, RM | Month (full name, abbreviated name, numeric, Roman numerals) |
IW, WW (ISO) | week number |
W | Day of the week |
DDD, DD, J | Day (of the year/of the month/Julian day) |
DAY, DY, D | Closest Sunday |
HH, HH12, HH24 | Hours |
MI | Minutes |
Return value
The TRUNC()
function returns a DATE
value truncated to a specified unit.
Examples
Let’s look at some examples of using the Oracle TRUNC()
function.
A) Truncate a date value using default format
Consider the following date time value:
04-Aug-2017 15:35:32
The following statement truncates the date value to midnight:
SELECT
TO_CHAR(
TRUNC(TO_DATE( '04-Aug-2017 15:35:32 ', 'DD-Mon-YYYY HH24:MI:SS' )),
'DD-Mon-YYYY HH24:MI:SS'
) result
FROM
dual;
Output:
RESULT
--------------------
04-Aug-2017 00:00:00
In this example,
- First, the
TO_DATE()
function converted a date string to aDATE
value. - Second, the
TRUNC()
function truncated the date. Because we did not pass the format argument, theTRUNC()
function uses the default value that truncates the date to midnight. - Third, the
TO_CHAR()
function formatted the result of theTRUNC()
function.
B) Get the first day of the month of a date
The following statement returns the first day of the current month.
SELECT
TRUNC( SYSDATE, 'MM' ) result
FROM
dual;
Output:
RESULT --------- 01-MAY-20
If you want to return the first day of the month of a specific date, you just need to use that date instead of the SYSDATE
C) Get the first day of the quarter of a date
Similarly, you can get the first day of the current quarter:
SELECT TRUNC( SYSDATE, 'Q' ) result
FROM dual;
Output:
RESULT --------- 01-APR-20
In this example, we replaced the month ( MM
) by quarter ( Q
).
In this tutorial, you have learned how to use the Oracle TRUNC()
function to truncates a date value to a specified unit.