TO_CHAR Function

The TO_CHAR function is the opposite of the TO_DATE function, and converts a date into a string of characters. Call TO_CHAR as follows:

TO_CHAR(date [,format])

The syntax elements are:

date

Specifies a PL/SQL variable or a database column of the DATE datatype. format Specifies the desired format of the output string. The format must be a valid com- bination of date format elements as described later in the section “Date Formats.”

The format is optional. When the format is not specified, the date is output in the default date format (as specified by NLS_DATE_FORMAT).

The following example uses TO_CHAR to convert an input date into a string using the default date format:

SELECT FNAME, TO_CHAR(HIRE_DATE) FROM EMPLOYEE;

Date Formats

We can display dates in a number of ways. Every country, every industry has its own standard of displaying dates. Oracle provides us with date format codes so that we can interpret and display dates in a wide variety of date formats.

A simple example of displaying a date is:

SELECT SYSDATE FROM DUAL;

By default, the date is displayed using the DD-MON-YY format. Let’s take another example to see how we can display a date in a format other than the default format:

SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;

The example converts the date into the format 'MM/DD/YYYY' with the TO_CHAR function. There are many ways to represent a date.