TO_CHAR and TO_DATE functions in SQL Oracle

TO_CHAR is a function which converts(/displays is best suited here) a Date to Character sequence. where as TO_DATE is a function which gives us a Date when we pass a character sequence as a date and correct format for that particular date.

Examples:

1) select to_date('January/10/2010', 'Month/DD/YYYY') from dual;

Result: 10-JAN-10.

Note that result is a Date.

Result format is based on the system format, my system format was 'DD-MON-YY'. Also we can change our system date format using alter session set NLS_DATE_FORMAT='DD-MON-YYYY'; statement for particular session only :) .

2) select to_char(sysdate, 'DD/MON/YYYY') from dual;

Result: 16/JAN/2010.

Note that result is a string or character sequence.