TO_DATE

The TO_DATE function converts a CHAR, VARCHAR2, CLOB, or NCLOB argument to a value of DATE data type.

SQL syntax

TO_DATE (Expression1[, Expression2 [, Expression3]])

Parameters

TO_DATE has the parameters:

Parameter Description

Expression1

A CHAR, VARCHAR2, CLOB, or NCLOB expression.

Expression2

The format string. This expression is usually required. It is optional only when Expression1 is in the default date format YYYY-MM-DD HHMISS.

Expression3

A CHAR or VARCHAR2 expression to specify the NLS parameter, which is currently ignored.

Description

You can use a datetime format model with the TO_DATE function. See "Datetime Format Models" for more information.

Examples

Command> SELECT TO_DATE ('1999, JAN 14', 'YYYY, MON DD') FROM dual;
< 1999-01-14 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('1999-12:23','YYYY-MM:DD')) FROM dual;
< 1999-12-23 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('12-23-1997 10 AM:56:20', 
                                'MM-DD-YYYY HH AM:MI:SS'), 
                                'MONTH DD, YYYY HH:MI-SS AM')
         FROM dual;
< DECEMBER  23, 1997 10:56-20 AM >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('12-23-1997 15:56:20', 
                                'MM-DD-YYYY HH24:MI:SS'), 
                                'MONTH DD, YYYY HH24:MI-SS')
         FROM dual;
< DECEMBER  23, 1997 15:56-20 >
1 row found.