TO_CHAR

Convert a numeric or date expression to a character String.

Syntax
      to_char(expression [,'format'] [,nls_format])

Key
   char      The DATE, NUMBER or expression to convert
   format    Format to use.
   nls_lang  The international language to use.

The format can be either a DATE format (YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute )
or a NUMBER format (0999=include leading zero).

If no format is specified Oracle will use the default date format.
nls_format allows international formats to be applied.

TO_CHAR will convert NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.

Examples

SQL> Select to_char(sysdate, 'yyyy/mm/dd') FROM dual;
 '2010/12/24'

SQL> Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
 'June 9, 2005'

SQL> select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;
 '14:35:56'

SQL> Select to_char(1.234, '9999.9') FROM dual;
 '1.2'

SQL> Select to_char(1000.25, '9,999.99') FROM dual;
 '1,000.25'

SQL> Select to_char(1000.25, '$9,999.00') FROM dual;
 '$1,000.25'

SQL> Select to_char(25, '000099') FROM dual;
 '000025'

SQL> Select to_char(-50, 'PR999') FROM dual;
 '<50>'

SQL> Select to_char(17, 'RN99') FROM dual;
 'XVII'

SQL> Select to_char('01110' + 1) FROM dual;
 1111

SQL> Select to_char(timestamp, 'DD-MM-YYYY HH24:MI') FROM dual;
 31-12-2005 23.30

Convert a character string into an Oracle date, then convert back to a string with a different date format:

SQL> Select to_char(mydate,'DD-MON-RR HH12:MI') Short_Date_Time
 from (
   select to_date('1-MAR-2010 23:24','DD-MON-RRRR HH24:MI') mydate
   from dual
 );

Related

TO_DATE - Convert to a date
TO_NUMBER - Convert to numeric format
CONVERT - Convert a string from one character set to another.
Oracle SQL Functions