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


6 comments:

  1. when drop the table the show error
    ora-00604(error occurred at recursive sql level 1)
    ora-01422(exact fetch return more than requested number of rows)

    ReplyDelete
    Replies
    1. Two questions:
      1. What drop command you are using ?
      2. How this post is related with your problem ?

      Delete
  2. Hi there, just became awaare of your blog throughh Google, and found that it's trulyy informative.
    I'm going to watch out for brussels. I will appreciate if you continue this in future.

    Lots of peopke will be benefited from your writing.

    Cheers!

    ReplyDelete
  3. I beloeve this web ssite holds some really good info for everyone
    :D.

    ReplyDelete
  4. Very nice post. I just stumbled upon your blog and wished to say that I have really enjoyed
    surfing around your blog posts. After all I will be
    subscribing to your rss feed and I hope you write
    again soon!

    ReplyDelete