Playing With Truncate and Date

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(SYSDATE -1,'DAY')+1 FROM DUAL;

TRUNC(TO_
---------
21-FEB-11


Related Posts:
- Why to Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
- Oracle: Some Important Date Queries
- Date Difference in Days, Months and Years
- Dates Difference in days, hours, minutes & seconds
- ORA-01830 date format picture ends before converting entire input string
- Oracle: Convert seconds to time - hh:mi:ss
- Oracle: convert time hh:mi:ss to seconds