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.
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 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.
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 25-22, "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. (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 statement, and again when the saved value of NLS_TERRITORY is restored.
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.
Capital letters in words, abbreviation, or Roman numerals in a format element produce corresponding capitalization in the return value. For example, the format element
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.
To convert dates with minimal formatting requirements, use CONVERT.
The following statement converts
A.M. to the default date format of
15JAN02, and stores that value in a DATE variable named
bonusdate = TO_DATE('January 15, 2002, 11:00 A.M.', - 'Month dd, YYYY, HH:MI A.M.')
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