Mastering Oracle SQL

Getting Dates In and Out of a Database

In the real world, dates are not always represented using Oracle’s DATE datatype. At various times, we’ll need to convert DATEs to other datatypes and vice versa. This is particularly true when we interface an Oracle database with an external system, for example when we are accepting date input from an external system in which dates are represented as strings of characters (or even as numbers), or when we are sending output from an Oracle database to another application that doesn’t understand Oracle’s DATE datatype. We also need to convert DATE values to text when we display dates on a screen or generate a printed report.

Oracle provides two extremely useful functions to convert dates:

• TO_DATE

• TO_CHAR

As their names suggest, TO_DATE is used to convert character data, or numeric data, into a DATE value, and TO_CHAR is used to convert a DATE value into a string of characters. Date formats, discussed later in this section, come in particularly handy for such conversions.

TO_DATE

TO_DATE is a built-in SQL function that converts a character string into a date. Input to the TO_DATE function can be a string literal, a PL/SQL variable, or a database column of the CHAR or VARCHAR2 datatype.

Call TO_DATE as follows:

TO_DATE(string [,format])

The syntax elements are:

string

Specifies a string literal, a PL/SQL variable, or a database column containing character data (or even numeric data) convertible to a date.

format

Specifies the format of the input string. ...