Introductionhttp://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html In this snippet, i will show you how to use the TRUNCATE function with DATE in Oracle. current date
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') CDATE FROM DUAL;
CDATE
--------------------
01-MAR-2011 16:54:25
date truncated to the 1st day of the year
SQL> SELECT TRUNC(SYSDATE, 'YEAR') YEAR FROM DUAL;
'YEAR'
-------------------
01-JAN-11
date truncated to the 1st day of the quarter
SQL> SELECT TRUNC(SYSDATE, 'Q') QUARTER FROM DUAL;
QUARTER
-------------------
01-JAN-11
date truncated to the 1st day of the month
SQL> SELECT TRUNC(SYSDATE, 'MONTH') MONTH FROM DUAL;
MONTH
-------------------
01-MAR-11
date truncated to the 1st day of the week
SQL> SELECT TRUNC(SYSDATE, 'DAY') DAY FROM DUAL;
DAY
-------------------
27-FEB-11
date truncated to the day
SQL> SELECT TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY HH24:MI:SS') TDATE FROM DUAL;
TDATE
--------------------
01-MAR-2011 00:00:00
date truncated to the hour
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'HH'),'DD-MON-YYYY HH24:MI:SS') THOUR FROM DUAL;
THOUR
--------------------
01-MAR-2011 16:00:00
date truncated to the minute
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'MI'),'DD-MON-YYYY HH24:MI:SS') TMINUTE FROM DUAL;
TMINUTE
--------------------
01-MAR-2011 16:53:00
date truncate to last monday
SQL> SELECT TRUNC( TO_DATE('27-FEB-2011') -1,'DAY')+1 FROM DUAL;
TRUNC(TO_
---------
21-FEB-11 |