Oracle SQL Fundamentals/Conversion Functions

This lesson introduces conversion functions.

Objectives and Skills edit

Objectives and skills for the conversion functions portion of Oracle SQL Fundamentals I certification include:^[1]

Using Conversion Functions and Conditional Expressions
- Describe various types of conversion functions that are available in SQL
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Test character conversion functions using HR data.
1. Run the following query:
  SELECT LAST_NAME, TO_CHAR(SALARY, '$99,999.00') AS SALARY, TO_CHAR(COMMISSION_PCT, 'V99') AS COMMISSION FROM EMPLOYEES;
2. Run the following query:
  SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DY MON DD YYYY') FROM EMPLOYEES;
3. Run the following query:
  SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'fmDay, Month D, YYYY') FROM EMPLOYEES;
Test date conversion functions using DUAL.
1. Run the following query:
  SELECT TO_DATE('012345', 'MMDDYY') AS "DATE" FROM DUAL;
2. Run the following query:
  SELECT TO_DATE('01/23/45', 'MM/DD/YY') AS "DATE" FROM DUAL;
Test number conversion functions using DUAL.
1. Run the following query:
  SELECT TO_NUMBER('$12,345.67', '$99,999.00') AS "NUMBER" FROM DUAL;
Test character conversion functions using OE data.
1. Select customer credit limit using different numeric formats.
2. Select order date using different date formats.
Test date conversion functions using DUAL.
1. Select various character-based date formats and convert the values to dates.
Test number conversion functions using DUAL.
1. Select various character-based numeric formats and convert the values to numbers.

TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. The value returned is always VARCHAR2.^[2]
TO_CHAR (n, [format]) converts n to a value of VARCHAR2 data type, using the optional number format.^[3]
TO_CHAR (datetime, [format]) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of VARCHAR2 data type in the format specified by the date format.^[4]
TO_NUMBER(expression, [format]) converts expression to a value of NUMBER data type. The expression can be a number value of CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE data type.^[5]
TO_DATE(char, [format]) converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.^[6]