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