The Oracle TRUNC (Date) function truncates the input date and returns a DATE as per truncating the time portion by the trunc_format input parameter.
I know that sounds confusing. So, let us understand the syntax of Oracle truncate date, its input parameters, return types in this Oracle Tutorial.
This page contains (Skip to topic)..
- Oracle TRUNC (Date) syntax
- Oracle TRUNC (Date) practical examples
- ORACLE TRUNC (Date) a point to remember
Oracle TRUNC (Date) syntax
TRUNC ( date [, trunc_format] )
Oracle TRUNC (Date) returns
The Oracle TRUNC function truncates the time portion of a date returns a DATE datatype.
Oracle TRUNC (Date) input arguments or parameters
- The date parameter is a date datatype. The DATE datatype in Oracle contains both date and time value in it.
- The trunc_format tells Oracle to truncate to the nearest day, month or year etc.
Oracle TRUNC (Date) more on trunc_format
- If trunc_format is omitted, then Oracle truncates to the nearest day.
- Oracle truncates to the left trunc_format and returns it to the beginning of the period. Oracle does not round them as in Oracle ROUND(Date) function.
Oracle TRUNC (Date) practical examples
Oracle TRUNC (Date) without trunc_format
select trunc(sysdate) from dual; TRUNC(SY -------- 30-09-18 ************* select to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual; TO_CHAR(SYSDATE,' ----------------- 30-SEP-2018 11:01 ************* select to_char(trunc(sysdate), 'DD-MON-YYYY HH24:MI') from dual; TO_CHAR(TRUNC(SYS ----------------- 30-SEP-2018 00:00
Oracle truncate function accepts DATE data and truncates its time portion. As we know that sysdate returns the current datetime, we are using it as the input parameter.
In the first example, we can not see if Oracle did truncate the time portion. Hence, we printed the sysdate by mentioning a format string ‘DD-MON-YYYY HH24:MI’ to the to_char function. Please check out the Oracle TO_CHAR(datetime) function for more details.
Lastly, we printed the datetime after truncating it. Now, we passed truncated sysdate as input to the TO_CHAR function along with format string. We see that the time portion is set to zero.
Here is another example,
select to_char( trunc(to_date('26-AUG-1996 03:00', 'DD-MON-YYYY HH24:MI') ), 'DD-MON-YYYY HH24:MI' ) "TRUNCATE TIME FROM DATE ?" from dual; TRUNCATE TIME FRO ----------------- 26-AUG-1996 00:00
Oracle TRUNC (Date) by MONTH as trunc_format
select trunc(to_date('26-AUG-1996', 'DD-MON-YYYY'), 'MONTH') "DOB MONTH START" from dual; DOB MONT -------- 01-08-96 --i.e. AUG 1st
Here, we are passing ‘MONTH’ as the trunc_format. Hence, Oracle truncates it to the first day of that month.
The “DOB MONTH START” is the Oracle column alias to the truncate expression.
Oracle TRUNC (Date) by YEAR as trunc_format
select trunc(to_date('26-AUG-1996', 'DD-MON-YYYY'), 'YEAR') "NEW YEAR" from dual; NEW YEAR -------- 01-01-96 --i.e. JAN 1st to the left
ORACLE TRUNC (Date) a point to remember
Oracle truncates towards the beginning of the trunc_format period.
The Oracle TRUNC function also works with Number. We will understand the Oracle truncate function with a number in the next post.