Conversion Functions

Conversion functions convert a value from one datatype to another datatype. Commonly, the form of the function names follows the convention datatype TO datatype. The first datatype is known as the input datatype and the latter datatype is the output datatype.  This category lists the SQL conversion functions.

TO_CHAR, date conversion

Syntax

TO_CHAR(d [, fmt])
It should Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified through the date format fmt. d is converted to a VARCHAR2 value in the default date format if fmt is not specified.

Example

SELECT TO_CHAR(SYSDATE,'Month DD, YYYY') "New date format" FROM dual;
New date format
------------------
December  04, 2000

TO_CHAR, number conversion

Syntax

TO_CHAR(n [, fmt])
It should Converts n of NUMBER datatype to a value of VARCHAR2 datatype by using the optional number format fmt. n is converted to a VARCHAR2 value exactly long enough to carry its significant digits if no fmt is provided.

Example

SELECT TO_CHAR(17145,'099G999') "Char" FROM DUAL
Char
------------------
017,145
TO_DATE Syntax
TO_DATE(char [, fmt])

It should Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. An fmt is a date format specifying the format of char. A char must be in the default date format if the fmt is not specified. For Julian If fmt is 'J', and then char must be a number.

Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a variant century value than the original char that is depending on fmt or the default date format.

Example

SELECT TO_DATE('September 25, 2000, 11:00 A.M.', ‘Month dd, YYYY, HH:MI A.M.') FROM DUAL
Output
TO_DATE('
---------
25-SEP-00