Interesting Features of Oracle TO_DATE and TRUNC Functions

advertisements

_____________________________________________________________________________________________________________________

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 1^st 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

So be careful when you combine these two functions without proper formatting. Otherwise, you end up with some unexpected results. J

Datapump Tuning Features

Datapump vs EXP/IMP

Datapump Advantages

Datapump Exclude Table Partition

Datapump Exclude and Include Parameter

Datapump NetworkLink Parameter

Datapump SQLFILE Parameter

Datapump Reuse_dumpfiles Parameter

Datapump Compression Parameter

Datapump Jobname Parameter

Datapump Remapdata Parameter

Datapump Sample Parameter

Datapump Query Parameter

Datapump Transportable Tablespace Option

_____________________________________________________________________________________________________________________

Website Stats