Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
to_date::=
TO_DATE
converts char
of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
datatype to a value of DATE
datatype. The fmt
is a date 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'
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.
Note: This function does not support |
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