TO_DATE

The TO_DATE function converts a formatted TEXT or NTEXT expression to a DATETIME value. This function is typically used to convert the formatted date output of one application (which includes information such as month, day, and year in any order and any language, and separators such as slashes, dashes, or spaces) so that it can be used as input to another application.

Return Value

DATETIME

Syntax

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

Arguments

text-exp

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.

fmt

A text expression that identifies a date format model. This model specifies how the conversion from text to DATE should be performed. For information about date format models, see the Oracle Database SQL Reference and the Oracle Database Globalization Support Guide.

The default value of fmt is the value of NLS_DATE_FORMAT.

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 23-20, "Specifying a Default Language and a Date Format". Do not use options that set other options. See "Specifying Options".

Notes

Similarity to SQL TO_DATE Function

The OLAP DML TO_DATE function has the same functionality as the SQL TO_DATE function. For more information about the SQL TO_DATE function, see Oracle Database SQL Reference.

Capitalization

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.

Output Format

The date value generated by TO_DATE has the format specified by the NLS_DATE_FORMAT option.

Default Date Format Values

The values of some format elements are determined by the value of the NLS_TERRITORY option. The language used for months and days is controlled by NLS_DATE_LANGUAGE.

Specifying Options

Options that set other options should not be used in this statement. For example, do not set NLS_LANGUAGE or NLS_TERRITORY. Set NLS_DATE_LANGUAGE instead. (See NLS Options for more information on these options.)

While TO_DATE will save and restore 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 will be 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 command, and again when the saved value of NLS_TERRITORY is restored.

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.

Simple Data Type Conversion

To convert dates with minimal formatting requirements, use CONVERT.

Examples

Example 23-19 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 23-20 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'
NLS_DATE_LANGUAGE = 'spanish'
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