SQL Statement - Conversion Functions

In addition to Oracle data types, columns of tables in an Oracle database can be defined by using the American National Standards Institute (ANSI), DB2, and SQL/DS data types. However, the Oracle server internally converts such data types to Oracle data types.

In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.
Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output.

Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

Implicit Data Type Conversion
Oracle server can automatically perform data type conversion in an expression. For example, the expression hire_date > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date. Therefore, a VARCHAR2 or CHAR value can be implicitly converted to a number or date data type in an expression.

In general, the Oracle server uses the rule for expressions when a data type conversion is needed. For example, the expression grade = 2 results in the implicit conversion of the number 20000 to the string “2” because grade is a CHAR(2) column.

Note: CHAR to NUMBER conversions succeed only if the character string represents a valid number.

Explicit Data Type Conversion
SQL provides three functions to convert a value from one data type to another:

Using the TO_CHAR Function with Dates

TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify.

        The format model must be enclosed with single quotation marks and is case-sensitive.
        The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
        The names of days and months in the output are automatically padded with blanks.
        To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM   employees
WHERE  last_name = 'Higgins';

Elements of the Date Format Model
Use the formats that are listed in the following tables to display time information and literals, and to change numerals to spelled numbers.

Using the TO_CHAR Function with Dates

SELECT last_name,
       TO_CHAR(hire_date, 'fmDD Month YYYY')
FROM   employees;

The SQL statement above  displays the last names and hire dates for all the employees. The hire date appears as 17 June 1987.


Modify the example  to display the dates in a format that appears as “Seventeenth of June 1987 12:00:00 AM.”

SELECT  last_name,
             'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
FROM    employees;

Notice that the month follows the format model specified; in other words, the first letter is capitalized and the rest are in lowercase.

Using the TO_CHAR Function with Numbers
When working with number values, such as character strings, you should convert those numbers to the character data type using the TO_CHAR function, which translates a value of NUMBER data type to VARCHAR2 data type. This technique is especially useful with concatenation.

        The Oracle server displays a string of number signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model.
        The Oracle server rounds the stored decimal value to the number of decimal places provided in the format model.

Using the TO_NUMBER and TO_DATE Functions

Convert a character string to a number format using the TO_NUMBER function:

TO_NUMBER(char[, 'format_model'])

Convert a character string to a date format using the TO_DATE function:

TO_DATE(char[, 'format_model'])

These functions have an fx modifier. This modifier specifies the exact match for the character argument and date format model of a TO_DATE function.

You may want to convert a character string to either a number or a date. To accomplish this task, use the TO_NUMBER or TO_DATE functions. The format model that you select is based on the previously demonstrated format elements.

The fx modifier specifies the exact match for the character argument and date format model of a TO_DATE function:

        Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
        The character argument cannot have extra blanks. Without fx, the Oracle server ignores extra blanks.
        Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without fx, the numbers in the character argument can omit leading zeros.


Display the name and hire date for all employees who started on May 24, 1999. There are two spaces after the month May and the number 24 in the following example. Because the fx modifier is used, an exact match is required and the spaces after the word May are not recognized:

SELECT last_name, hire_date
FROM   employees
WHERE  hire_date = TO_DATE('May   24, 1999', 'fxMonth DD, YYYY');

The error:

Using the TO_CHAR and TO_DATE Function with RR Date Format
To find employees who were hired before 1990, the RR format can be used. Because the current year is greater than 1999, the RR format interprets the year portion of the date from 1950 to 1999.

The following command, on the other hand, results in no rows being selected because the YY format interprets the year portion of the date in the current century (2090).

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy')
FROM   employees
WHERE  TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-1990';