Using Conversion Functions in Queries

Conversion functions convert one data type to another. The conversion functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The query in the following example uses the TO_CHAR function to convert HIRE_DATE values (which are of type DATE) to character values that have the format FMMonth DD YYYY. FM removes leading and trailing blanks from the month name. FMMonth DD YYYY is an example of a format format model.

Converting Dates to Characters Using a Format Template

SELECT LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMMonth DD YYYY') "Date Started"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                 HIRE_DATE Date Started
------------------------- --------- -----------------
Chen                      28-SEP-97 September 28 1997
Faviet                    16-AUG-94 August 16 1994
Greenberg                 17-AUG-94 August 17 1994
Popp                      07-DEC-99 December 7 1999
Sciarra                   30-SEP-97 September 30 1997
Urman                     07-MAR-98 March 7 1998
 
6 rows selected.

The query in the following example uses the TO_CHAR function to convert HIRE_DATE values to character values that have the two standard formats DS (Date Short) and DL (Date Long).

Converting Dates to Characters Using Standard Formats

SELECT LAST_NAME,
TO_CHAR(HIRE_DATE, 'DS') "Short Date",
TO_CHAR(HIRE_DATE, 'DL') "Long Date"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Short Date Long Date
------------------------- ---------- -----------------------------
Chen                      9/28/1997  Sunday, September 28, 1997
Faviet                    8/16/1994  Tuesday, August 16, 1994
Greenberg                 8/17/1994  Wednesday, August 17, 1994
Popp                      12/7/1999  Tuesday, December 07, 1999
Sciarra                   9/30/1997  Tuesday, September 30, 1997
Urman                     3/7/1998   Saturday, March 07, 1998
 
6 rows selected.

The query in the following example uses the TO_CHAR function to convert SALARY (which are of type NUMBER) to character values that have the format $99,999.99. $99,999.99 is an example of a datetime format model.

Converting Numbers to Characters Using a Format Template

SELECT LAST_NAME,
TO_CHAR(SALARY, '$99,999.99') "Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY SALARY;

Result:

LAST_NAME                 Salary
------------------------- -----------
Popp                        $6,900.00
Sciarra                     $7,700.00
Urman                       $7,800.00
Chen                        $8,200.00
Faviet                      $9,000.00
Greenberg                  $12,000.00
 
6 rows selected.

The query in the following example uses the TO_NUMBER function to convert POSTAL_CODE values (which are of type VARCHAR2) to values of type NUMBER, which it uses in calculations.

Converting Characters to Numbers

SELECT CITY,
POSTAL_CODE "Old Code",
TO_NUMBER(POSTAL_CODE) + 1 "New Code"
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'
ORDER BY POSTAL_CODE;

Result:

CITY                           Old Code       New Code
------------------------------ ------------ ----------
Southlake                      26192             26193
South Brunswick                50090             50091
Seattle                        98199             98200
South San Francisco            99236             99237
 
4 rows selected.

The query in the following example uses the TO_DATE function to convert a string of characters whose format is Month dd, YYYY, HH:MI A.M. to a DATE value.

Converting a Character String to a Date

SELECT TO_DATE('January 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;

Result:

Date
---------
05-JAN-07

The query in the following example uses the TO_TIMESTAMP function to convert a string of characters whose format is DD-Mon-RR HH24:MI:SS.FF to a TIMESTAMP value.

Converting a Character String to a Time Stamp

SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;

Result:

Timestamp
------------------------------------------------------------------------------
05-MAY-07 08.43.00.000000000 AM

Using Conversion Functions in Queries

Related Topics