Interesting Features of Oracle TO_DATE and TRUNC Functions

My current system date is 04-May-2012.
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
04-MAY-12

When I select to_date('2012', 'YYYY') it gives 1st day of current month.
SQL> select to_date('2012', 'YYYY') from dual;

TO_DATE('
---------
01-MAY-12

When I select to_date('05','MM') it gives 1st day of the 05th month.
SQL> select to_date('05','MM')  from dual;

TO_DATE('
---------
01-MAY-12

When you combine to_date function with Trunc function you will get 1st day of the year.
SQL> select trunc(to_date('2012','YYYY'),'YEAR' ) FROM DUAL;

TRUNC(TO_
---------
01-JAN-12

SQL>  select trunc(to_date('2012','YYYY'),'YEAR' ), to_date('2012','YYYY') FROM DUAL;

TRUNC(TO_ TO_DATE('
--------- ---------
01-JAN-12 01-MAY-12

SQL> select trunc(to_date('05','MM'), 'YYYY') from dual;

TRUNC(TO_
---------
01-JAN-12

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google