Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
TO_DATE
converts char
of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
datatype to a value of DATE
datatype. The fmt
is a datetime model format specifying the format of char
. If you omit fmt
, then char
must be in the default date format. If fmt
is J
, for Julian, then char
must be an integer.
Note: This function does not convert data to any of the other datetime datatypes. For information on other datetime conversions, please refer to TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, and TO_YMINTERVAL. |
The default date format is determined implicitly by the NLS_TERRITORY
initialization parameter or can be set explicitly by the NLS_DATE_FORMAT
parameter.
The 'nlsparam'
argument has the same purpose in this function as in the TO_CHAR
function for date conversion.
Do not use the TO_DATE
function with a DATE
value for the char
argument. The first two digits of the returned DATE
value can differ from the original char
, depending on fmt
or the default date format.
This function does not support CLOB
data directly. However, CLOB
s can be passed in as arguments through implicit data conversion.
Examples
The following example converts a character string into a date:
SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; TO_DATE(' --------- 15-JAN-89
The value returned reflects the default date format if the NLS_TERRITORY
parameter is set to 'AMERICA
'. Different NLS_TERRITORY
values result in different default date formats:
ALTER SESSION SET NLS_TERRITORY = 'KOREAN'; SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; TO_DATE( -------- 89/01/15