Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Conversion Functions
Oracle Tips by Burleson

Oracle provides conversion functions so values of one datatype can be easily converted to another datatype. While Oracle will perform implicit conversions of datatypes for you, this could lead to performance problems in your applications and to compatibility problems in future versions of Oracle. I strongly recommend that you use these conversion functions instead of relying on Oracle’s implicit conversions.


The to_char() function is probably the most commonly used conversion function. The function converts both numerical data and date data to datatype varchar2.

The simplest way of using to_char() to convert date information is as follows:

to_char ('02/14/97')

This converts the date into a character string in the default date format (numerical month, numerical day, and a two-digit year, separated by slashes). However, the most common use of to_char() is to convert dates to type varchar2 in a specific format, as shown in this example,

to_char ('14-FEB-97', 'DD-MON-YYYY')

which returns the following string:


Using to_char() with numerical data is very similar to using the function with dates. One common use is to simply convert data from a numerical datatype to type varchar2. For example,

to_char (25000)



When converting numerical data, to_char() can also take a second argument, a format model for the output of the function. For example,

to_char (25000, '$99,999.99')



Chapter 3 lists the elements that can be used in format models for numerical and date data.


The to_date() function is used to convert character data to the date datatype. Like to_char(), this function can be called with a single parameter, much like

to_date ('02-MAY-97')

which returns a value of type date. to_date() may also be called with a second parameter, which instructs the function to convert the specified string from the specified format into a standard date. For example,

to_date ('02 May 1997', 'DD MONTH YYYY')



Valid elements of the format model are discussed in Chapter 3. The examples provided here have all used the default Oracle date format.


The to_number() function converts data of type char or varchar2 to type number. The function can accept a single parameter. For example,

to_number ('25000')



The function can also accept a format model as the second parameter, like the to_char() and to_date() functions. For example,

to_number ('25000', '$99,999.99')



Valid elements of the format model are discussed in Chapter 3.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

For more information on Oracle conversion functions, please visit the following links:

Oracle Convert

Oracle Convert to number



Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter