Convert a numeric or date expression to a character String.

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

   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.


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

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;

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

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

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

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

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

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

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

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


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

© Copyright 1999-2015
Some rights reserved