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;

Specifying a date format

If we wish to specify a date format, there are at least two approaches we can take:

• Specify the format at the session level, in which case it applies to all implicit con- versions, and to all TO_DATE conversions for which we do not explicitly spec- ify some other format.

• Specify the format as a parameter to a TO_DATE call. The following example changes the default date format for the session, and then uses TO_DATE to convert a number to date.

  

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY';

Session altered.

INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE(102299));