How to use the TRUNCATE and DATE in Oracle Query

No.of Views4208
Bookmarked0 times
Downloads 
Votes0
By  Nimish Garg   On  05 Mar 2011 05:03:57
Tag : Oracle , T-SQL
In this snippet, i will show you how to use the TRUNCATE function with DATE in Oracle.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

http://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

 
Sign Up to vote for this article
 
About Author
 
Nimish Garg
Occupation-Software Engineer
Company-Indiamart Intermesh Limited
Member Type-Junior
Location-India
Joined date-04 Jun 2010
Home Page-http://nimishgarg.blogspot.com/
Blog Page-http://nimishgarg.blogspot.com/
Oracle DBA (Performance Tuning and Query Optimization) IndiaMART InterMESH Limited, Noida
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top