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

Oracle: Some Important Date Queries
http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html

Oracle: Dates Difference in days, hours, minutes & seconds
http://nimishgarg.blogspot.com/2009/12/get-dates-difference-in-days-hours.html

Oracle SQL: Date Difference in Days, Months and Years
http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html