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.
Guidelines
•
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')
AS HIREDATE
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.
Example:
Modify the
example to display the dates in a format
that appears as “Seventeenth of June 1987 12:00:00 AM.”
SELECT last_name,
TO_CHAR(hire_date,
'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
HIREDATE
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.
Example:
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';
