Purpose
TO_DATE() converts CHAR, VARCHAR, NCHAR, or NVARCHAR2 characters into a value of the date data type.
Syntax
TO_DATE(char [, fmt [, 'nlsparam' ] ])
Parameters
| Parameter | Description |
|---|---|
| char | A value of the CHAR, VARCHAR, NCHAR, or NVARCHAR2 data type. |
| fmt | Optional. Specifies the time format of char. If fmt is omitted, char must use the default date format, which is specified implicitly in the initialization parameter nls_territory or explicitly in the nls_date_format parameter. |
| nlsparam | Optional. Specifies the language of the text string to be converted into a date. |
Return type
This function returns the DATE data type.
Examples
Convert strings 202111, 2021.11.11, and 2021-11-11 11:11:11 to a date data type.
obclient> SELECT TO_DATE('202111','YYYYMM'),
TO_DATE('2021.11.11','YYYY.MM.DD'),
TO_DATE('2021-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS')
FROM DUAL;
+----------------------------+------------------------------------+------------------------------------------------------+
| TO_DATE('202111','YYYYMM') | TO_DATE('2021.11.11','YYYY.MM.DD') | TO_DATE('2021-11-1111:11:11','YYYY-MM-DDHH24:MI:SS') |
+----------------------------+------------------------------------+------------------------------------------------------+
| 01-NOV-21 | 11-NOV-21 | 11-NOV-21 |
+----------------------------+------------------------------------+------------------------------------------------------+
1 row in set