ORACLE TO_DATE

The Oracle TO_DATE function is used for converting text strings with VARCHAR2 data type to DATE data type.

Syntax

The syntax of Oracle TO_DATE function is as follows:

TO_DATE ( text, format )

Here,

Text – It is the date in text literal or character string that we want to convert to DATE data type recognized by Oracle. It can be any of the following data types: CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Format – This is the format in which we want the date to be. If we don’t give the format then Oracle will return the default format which is: DD-MM-YYYY

The TO_DATE will convert the date in text format to DATE data type.

Examples

1. TO_DATE function for converting Date in Text format

We need to keep the Oracle date formats in mind to convert any text string to date as the string needs to adhere to Oracle formats. For example, DD for Day, MM for Month, YYYY for year etc.

Suppose we have the following date in string format:

01 Feb 2016

As we can see it is in, DD-MM-YYYY format already and we can apply TO DATE function to convert it into DATE data type. Suppose this wasn’t in an Oracle recognized format, then we first need to convert it into DD-MM-YYYY format.

Then, we apply the TO_DATE function to convert this from string to DATE.

SELECT
TO_DATE( '01 Feb 2016', 'DD MON YYYY' )
FROM
dual;

Result:

The query converts the date string to DATE date type.

Remember to give correct date format, as not giving the correct format may result in the following error:

ORA-01830: date format picture ends before converting entire input string

2. Using TO_DATE function for inserting data into a Table

Lets create a new table to see this example:

CREATE TABLE friends_cast(
friend_id NUMBER,
name VARCHAR2( 255 ),
join_date DATE NOT NULL,
PRIMARY KEY (friend_id)
);

Now, Lets insert a new row into the table using this query:

INSERT INTO friends_cast(friend_id, name, join_date)
VALUES(1 ,'Phoebe', TO_DATE('Feb 11 1993','Mon DD YYYY'));

Since, the string is not in an Oracle recognized format we use TO_DATE function to first convert it into a DATE before inserting it into the table.

Result:

Similarly, we can insert other members into the table using similar queries. Lets have a look at one more for example.

INSERT INTO friends_cast(friend_id, name, join_date)
VALUES(1,'Ross', TO_DATE('Feb 01 1993','Mon DD YYYY'));

Result:

Finally, both the values have been inserted in the table.

SELECT * FROM friends_cast;

Result:

In this tutorial we learned how to use TO_DATE function to convert date string to DATE data type.