Oracle SQL - TO_CHAR function


To format a date, use the TO_CHAR function.

TO_CHAR formats a date data type to a string by an format string.

The syntax of TO_CHAR is:

TO_CHAR(column name in date data type, format) 

Here is an example of TO_CHAR being used in a SELECT statement:


SQL> SELECT empno, ename, TO_CHAR(hiredate, 'dd Month yyyy')
  2  FROM employee
  3--  www.  j a  v a2 s. co m

An alias is required when using TO_CHAR to "pretty up" the output:

SELECT empno, ename, 
TO_CHAR(hiredate, 'dd Month yyyy') "Hiredate" 
FROM employee;

The following table illustrates some TO_CHAR date formatting. 
Format Will look like
dd Month yyyy 05 March 2006
dd month YY 05 march 06
dd Mon 05 Mar
dd RM yyyy 05 III 2003
Day Mon yyyy Sunday Mar 2006
Day fmMonth dd, yyyy Sunday March 5, 2006
Mon ddsp yyyyMar five 2006
ddMon yy hh24:mi:ss 05Mar 06 00:00:00
SELECT TO_CHAR(SYSDATE, 'dd Mon, yyyy hh24:mi:ss') 
FROM dual;

Related Topics