TRUNC function in oracle to truncate the date to the nearest unit

By Santhosh N

This explains how to truncate the date to the nearest year, month, date, etc...

There is function by the name, TRUNC which is used to get the truncated date from the given date to the nearest year, month, date or day in the week as per the desired format specified.

Syntax: TRUNC(date, [format])

Here, date is the specified date and format is the unit of measure that needs to be applied for returning the truncated date from the date specified. However, if the second parameter, format is omitted the date is truncated to the day value, i.e., hours, minutes and seconds will be truncated.

And the allowed format specifiers are as follows:

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


Ex1: SELECT TRUNC(TO_DATE('25-MAY-10'),’YEAR’) FROM dual;
This returns “01-JAN-10”

Ex2: SELECT TRUNC(TO_DATE('25-MAY-10'),’MONTH’) FROM dual;
This returns “01-MAY-10”

Related FAQs

This explains how to get the offset value of the specified time zone in oracle.
This explains how to get the next weekday name’s date from the specified date.
This explains how to convert the date and time from one time zone to other and return the equivalent time to desired time zone.
This explains the difference between two oracle date functions namely LOCALTIMESTAMP and CURRENT_TIMESTAMP.
This explains how to get the current date and time in the time zone of the current SQL session which is set by the Alter session command.
This explains how to convert the timestamp value to the timestamp with time zone in oracle.
TRUNC function in oracle to truncate the date to the nearest unit  (5310 Views)