Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The TO_DATE function converts a formatted TEXT or NTEXT expression to a DATETIME value.

Return Value



TO_DATE(text-exp, [fmt,] [option setting])



The text expression that contains a date to be converted. The expression can have the TEXT or NTEXT data type. A conversion from NTEXT can result in an incorrect result when the NTEXT value cannot be interpreted as a date.


A text expression that identifies a datetime format template. This template specifies how the conversion from text to DATETIME should be performed. For information about datetime format templates, see Table 9-4, "Datetime Format Elements".

option setting

An OLAP option (such as NLS_DATE_LANGUAGE) and its new setting, which temporarily overrides the setting currently in effect for the session. Typically, this option identifies the language of text-exp when it is different from the session language. See Example 8-140, "Specifying a Default Language and a Date Format".

Do not specify an option that sets other options. For example, do not set NLS_LANGUAGE or NLS_TERRITORY; set NLS_DATE_LANGUAGE instead. While TO_DATE saves and restores the current setting of the specified option so that it has a new value only for the duration of the statement, TO_DATE cannot save and restore any side effects of changing that option. For example, NLS_TERRITORY controls the value of NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_CALENDAR, and other options. When you change the value of NLS_TERRITORY in a call to TO_DATE, all of these options are reset to their territory-appropriate default values twice: once when NLS_TERRITORY is set to its new value for the duration of the TO_DATE statement, and again when the saved value of NLS_TERRITORY is restored.

Usage Notes


Capital letters in words, abbreviation, or Roman numerals in a format element produce corresponding capitalization in the return value. For example, the format element DAY produces MONDAY, Day produces Monday, and day produces monday.

Unrecognized Dates

When TO_DATE cannot construct a value with a valid DATE value using fmt, it returns an error. For example, when an alphanumeric character appears in text-exp where fmt indicates a punctuation character, then an error results.


Example 8-139 Converting Text Values to DATE Values

The following statement converts January 15, 2002, 11:00 A.M. to the default date format of 15JAN02, and stores that value in a DATE variable named bonusdate.

bonusdate = TO_DATE('January 15, 2002, 11:00 A.M.', -
   'Month dd, YYYY, HH:MI A.M.')

Example 8-140 Specifying a Default Language and a Date Format

The following statements set the default language to Spanish and specify a new date format. The NLS_DATE_LANGUAGE option, when used in the TO_DATE function, allows the American month name to be translated.

NLS_DATE_FORMAT = 'Day: Month dd, yyyy HH:MI:SS am'
SHOW TO_DATE('November 15, 2001', 'Month dd, yyyy', -
   NLS_DATE_LANGUAGE 'american')

The date is translated from American to Spanish and displayed in the new date format.

Jueves   : Noviembre  15, 2001 12:00:00 AM