Skip to content Skip to main navigation Skip to footer

SQL Conversion Functions

Objectives

This tutorial covers the following objectives:

  • Provide an example of an explicit data-type conversion and an implicit data-type conversion
  • Explain why it is important, from a business perspective, for a language to have built-in data-conversion capabilities
  • Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER, and TO_DATE single-row functions to produce a desired result
  • Apply the appropriate date and/or character format model to produce a desired output
  • Explain and apply the use of YY and RR to return the correct year as stored in the database

Purpose

  • Imagine having to read all your school books in text files with no paragraphs and no capitalization.
  • It would be difficult to read.
  • Fortunately, there are software programs available to capitalize and color text, underline, bold, center, and add
  • graphics.
  • For databases, format and display changes are done using conversion functions.
  • These functions are able to display numbers as local currency, format dates in a variety of formats, display time to the second, and keep track of what century a date refers to.

Data Types

  • When a table is created for a database, the SQL programmer must define what kind of data will be stored in each field of the table.
  • In SQL, there are several different data types. These data types define the domain of values that each column can contain.
  • For this article, you will use:
    • VARCHAR2
    • CHAR
    • NUMBER
    • DATE

Most programming languages require the programmer to declare the data type of every data object. For the data stored in a database, the SQL programmer defines a data type for every column in the database.

Data types will be covered in more detail later in the course, but for now we will be using VARCHAR2, CHAR, NUMBER, and DATE.

Data Types Described

  • VARCHAR2: Used for character data of variable length, including numbers, dashes, and special characters.
  • CHAR: Used for text and character data of fixed length, including numbers, dashes, and special characters.
  • NUMBER: Used to store variable-length numeric data. No dashes, text, or other nonnumeric data are allowed. Currency is stored as a number data type.
  • DATE: Used for date and time values. Internally, Oracle stores dates as numbers and, by default, DATE information is displayed as DD-Mon-YYYY (for example, 23-Oct-2013).

Type Conversion

  • The Oracle Server can automatically convert VARCHAR2 and CHAR data to NUMBER and DATE data types.
  • It can convert NUMBER and DATE data back to CHARACTER data type.
  • This is know as implicit data conversion
SQL Conversion Functions

Implicit: something that is implied but not directly expressed.

  • Although this is a convenient feature, it is always best to explicitly make data type conversions to ensure reliability in SQL statements.
Implicit data type conversions

Explicit: clearly formulated or defined.

  • The four data type conversion functions you will learn are:
    • To convert date data type to character data type
    • To convert number data type to character data type
    • To convert character data type to number data type
    • To convert character data type to date data types
EXPLICIT DATA TYPE CONVERSION

Date Conversion to Character Data

  • It is often desirable to convert a date from its default DD-Mon-YYYY format to another format specified by you.
  • The function to accomplish this task is:
TO_CHAR (date column name, 'format model you specify')
  • The ‘format model’ must be enclosed in single quotation marks and is case-sensitive.
  • Separate the date value from the format model with a comma.
  • Any valid date format element can be included.
  • Use sp to spell out a number.
  • Use th to have the number appear as an ordinal.
    • (1st, 2nd, 3rd, and so on)
  • Use an fm element to remove padded blanks or remove leading zeroes from the output.
  • The tables show the different format models that can be used.
  • When specifying time elements, note that hours (HH), minutes (MI), seconds (SS), and AM or PM can also be formatted.
Date Conversion to Character Data
  • Examples of output using different format models:
Date Conversion to Character Data

Example 2 uses “fm” to suppress leading zeros in the date.

Example 3 adds “th” etc. to the display the day as an ordinal number.

  • Second table showing examples of output using different format models
Date Conversion to Character Data

Example 2 adds “SP to the day format to spell out the day portion of the date.

Example 3 displays the year spelled out, and the text “of” between the day and month. Note that double quotes are required around the text literal to be included.

  • Examples of output using different format models for time:
Date Conversion to Character Data

Number Conversion to Character Data (VARCHAR2)

  • Numbers stored in the database have no formatting.
  • This means that they have no currency signs/symbols, commas, decimals, or other formatting.
  • To add formatting, you first need to convert the number to a character format.
TO_CHAR(number, 'format model')
  • The SQL function that you use to convert a number to a desired character format is:
  • The table illustrates some of the format elements available to use with TO_CHAR functions.
Number Conversion to Character Data (VARCHAR2)
SELECT TO_CHAR(salary,
'$99,999') AS "Salary"
FROM employees;
Number Conversion to Character Data (VARCHAR2)

NOTE: if the number of “9”s in the format model is less than the number of digits in the number, ##### is displayed, for example SELECT TO_CHAR(24000, ‘$9,999’) would return ####### as the format model has four “9”s, but the number supplied has five digits.

  • Can you identify the format models used to produce the following output?
    • $3000.00
    • 4,500
    • 9,000.00
    • 0004422

Answer:

Number Conversion to Character Data (VARCHAR2)

Character Conversion to Number

  • It is often desirable to convert a character string to a number. The function for this conversion is:
TO_NUMBER(character string, 'format model')
  • The format model is optional, but should be included if the character string being converted contains any characters other than numbers.
  • You cannot reliably perform calculations with character data.
SELECT TO_NUMBER('5,320', '9,999')
AS "Number"
FROM dual;
Character Conversion to Number
  • The bonus column includes data which contains 4 characters, the format model specifies 3 characters, so an error is returned.

Oracle Application Express will return an Oracle Error — Invalid Number — if the format model does not match the number returned by the database.

SELECT last_name, TO_NUMBER(bonus, '999')
FROM employees
WHERE department_id = 80;
SELECT last_name, TO_NUMBER(bonus, '9999')
AS "Bonus"
FROM employees
WHERE department_id = 80;
Character Conversion to Number

Character Conversion to Date

  • To convert a character string to a date format, use:
TO_DATE('character string', 'format model')
  • This conversion takes a non-date value character string such as “November 3, 2001” and converts it to a date value.
  • The format model tells the server what the character string “looks like”:
  • – will return 03-Nov-2001.
TO_DATE('November 3, 2001', 'Month dd, yyyy') 
  • When making a character-to-date conversion, the fx (format exact) modifier specifies exact matching for the character argument and the date format model.
  • In the following example, note that “May10″ has no space between ”May” and “10.”
  • The fx format model matches the character argument as it also has no space between “Mon” and “DD.”

fx Modifier Rules

  • The fx modifier rules are:
    • Punctuation and quoted text in the character argument must match the corresponding parts of the format model exactly (except for case).
    • 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, numbers in the character argument can omit leading zeros.
fx Modifier Rules

RR Date Format and YY Date Format

  • All date data should now be stored using four-digit years (YYYY).
  • Some legacy databases however may still use the twodigit (YY) format.
  • It has not been that long since the century changed from 1900 to 2000.
  • Along with this change came considerable confusion as to whether a date written as 02-Jan-98 would be interpreted as January 2, 1998 or January 2, 2098.
  • If the data being converted from character data to date data contains only a two-digit year, Oracle has a way of interpreting these dates in the correct century.
  • For example: ’27-Oct-95′
SELECT TO_DATE('27-Oct-95','DD-Mon-YY')
AS "Date"
FROM dual;
  • The two-digit year is interpreted as 2095, this may not be what was intended.
  • If YY is used in the format model, the year is assumed to be in the current century.
  • If the two-digit year is not in the current century, we use RR.
SELECT TO_DATE('27-Oct-95','DD-Mon-RR')
AS "Date"
FROM dual;
RR Date Format and YY Date Format
  • The two-digit year is now interpreted as 1995.

A Few Simple Rules

  • If the date format is specified with the RR format, the return value has two possibilities, depending on the current year.
  • If the current year is between 00-49:
    • Dates from 0-49: The date will be in the current century
    • Dates from 50-99: The date will be in the last century
A Few Simple fx Rules
  • If the current year is between 50-99:
    • Dates from 0-49: The date will be in next century
    • Dates from 50-99: The date will be in current century
  • The table below gives some examples of how YY and RR are interpreted, depending on the current year
The table below gives some examples of how YY and RR are interpreted, depending on the current year
  • When I query my employee database using the following statement, it returns every row in the table.
  • I know there are only a few employees who were hired before 1990.
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-YY');
  • As the format model in the WHERE clause uses YY, and the current year is 2017, the query returns rows with a hire_date less than 2090.

This can be fixed by replacing YY in the format model with RR, or by using four-digit years YYYY.

Terminology

Key terms used in this tutorial included:

  • CHAR
  • DATE
  • DD date format
  • Conversion function
  • fm
  • NUMBER
  • RR date format
  • TO_CHAR
  • TO_DATE
  • TO_NUMBER
  • VARCHAR2
  • Fx Modifier

Summary

In this tutorial, you should have learned how to:

  • Provide an example of an explicit data-type conversion and an implicit data-type conversion
  • Explain why it is important, from a business perspective, for a language to have built-in data-conversion capabilities
  • Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER and TO_DATE single-row functions to produce a desired result
  • Apply the appropriate date and/or character format model to produce a desired output
  • Explain and apply the use of YY and RR to return the correct year as stored in the database

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *