Oracle TO_DATE() Function

Oracle TO_DATE() is a built-in function that converts a given string date to a value of the DATE data type according to an optional format.

If you need to convert data to other date-time data types, use TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, and TO_YMINTERVAL.

Oracle `TO_DATE()` Syntax

Here is the syntax of the Oracle TO_DATE() function:

TO_DATE(char [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

char: Required. It can be any character string expression that evaluates to the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
DEFAULT return_value ON CONVERSION ERROR: Optional. It allows you to specify the value to be returned if a conversion error occurs.
fmt: Optional. Format string. It is recommended to always provide this format parameter.
'nlsparam': Optional. You can use this 'NLS_DATE_LANGUAGE = language' form to set this parameter, where language is the language name.

The Oracle TO_DATE() function converts the given string date to a value of the DATE data type according to an optional format.

If any of the parameters is NULL, TO_DATE() will return NULL.

Here are a few examples showing how to use the Oracle TO_DATE() function.

The following example converts a date to a value of the DATE data type:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT
    TO_DATE('2023/03/01', 'YYYY-MM-DD')
FROM dual;

输出：

TO_DATE('2023-03-01','YYYY-MM-DD')
_____________________________________
2023-03-01 00:00:00

If any of the parameters is NULL, TO_DATE() will return NULL.

SET NULL 'NULL';
SELECT
    TO_DATE(NULL)
FROM dual;

输出：

TO_DATE(NULL)
________________
NULL

In this example, we use the SET NULL 'NULL'; statement to display the NULL value as the string 'NULL'.

Oracle TO_DATE() is a built-in function that converts a given string date to a value of the DATE data type according to an optional format.