Oracle Convert Varchar2 to Date

Oracle Convert Varchar2 to Date | In this post we will see how to convert data type Varchar2 to Date. There are two possibilities if the table contains the data and if you want to change the datatype then there are certain steps to follow. Another possibility is when the table is empty and to change the data type.

Oracle Convert Varchar2 to Date for Column

First, let us see after the creation of the table (when data is not inserted) how to convert varchar2 to date in oracle.

CREATE TABLE test (
   id NUMBER,
   hiredate VARCHAR2(100)
);

Using the DESC command we can see the structure of the table.

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              VARCHAR2(100)

Currently, the name column is of VARCHAR2 type and we want to change it in the DATE data type.

ALTER TABLE test
MODIFY hiredate DATE;

When the column/table is null / no records in that column/table then the above query can be used to modify the datatype.

In this result, the table is altered which means the hiredate column of type varchar2 is modified to the Date datatype using the ALTER TABLE and by using the MODIFY keyword.

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              DATE

Oracle Convert Varchar2 to Date for Column – When Table Contains Data

Now, we will look into the second possibility that the table/column has some records and now we need to change the varchar2 datatype to the date datatype for the hiredate column.

CREATE TABLE test1 (
   id NUMBER,
   hiredate VARCHAR2(50)
);
INSERT INTO test1 VALUES(10, '10/12/2025');
INSERT INTO test1 VALUES(20, '15/08/2030');
SELECT * FROM test1;

Output:-

 ID HIREDATE
--- ----------
 10 10/12/2025
 20 15/08/2030

Let’s follow certain steps to achieve the modification:-

Step 1: We will use the ALTER keyword to alter the table by adding another column called “hiring date” of datatype date.

Here is the Query to add another column to the table.

ALTER TABLE test1
ADD hiringdate DATE;

Now the table is altered i.e the column hiring date of type date is added to the TEST1 table. We can confirm the addition of a column by using the DESC query.

DESC test1;

Output:-

Name           Null?   Type
------------- -------- -------------
ID                     NUMBER
HIREDATE               VARCHAR2(100)
HIRINGDATE             DATE

Step-2: In this step, we will use the UPDATE keyword to update the “hiringdate” column and set the data of hiredate in the date format to the “hiring date” column.

Simply we can say coping the data from “hiredate” to the newly added column “hiringdate”. Here is the query for updating and setting the data.

UPDATE test1 SET 
hiringdate = TO_DATE(HIREDATE,'DD/MM/YYYY');

Output:-

2 rows updated.

We use the to_date() function to set the format of the date. We can check those hiring date data from the following query.

SELECT * FROM test1;

Output:-

ID HIREDATE     HIRINGDAT
-- ------------ ---------
10 10/12/2025   10-DEC-25
20 15/08/2030   15-AUG-30

From the above result, we can find that the “hiredate” and “hiringdate” are matching as we used the set keyword to have the same copy of the “hiredate” in the “hiringdate” column.

Step3: In this step, we will drop the “hiredate” column of type varchar as we copied all the data to the hiring date.

Query to drop the column from the emp table using alter and drop keyword.

ALTER TABLE test1 DROP (hiredate);

Step4: In this step, we will rename the “hiringdate” of the date type to the old column name hiredate.

Query to rename the column from hiring date to hiredate.

ALTER TABLE test1 
RENAME COLUMN hiringdate TO hiredate;

In this result, we can say that the “hiringdate” column renaming has been done to the “hiredate” and contains all the records. This is how we convert the varchar2 to date in Oracle when a table contains the data.

Convert String to Datetime Oracle

When you want to convert the String to Date time oracle, here is the query to follow:-

To convert from string to date time we use the to_char() function and to_date() function. By executing the following query.

Query to Convert String to Datetime Oracle

SELECT 
to_date('15/08/2025', 'DD/MM/YYYY')
FROM dual;

Output:-

TO_DATE('
---------
15-AUG-25

In the above query ’15/08/2025′ was a string that is converted to the date type. Let us see another example using a table.

CREATE TABLE test2 (
   id NUMBER,
   hiredate VARCHAR2(50)
);
INSERT INTO test2 VALUES(10, '10/12/2025');
INSERT INTO test2 VALUES(20, '15/08/2030');

The table is created with “id” and “hiredate” columns. The “id” column is of number type and “hiredate” is of varchar2 type.

Query to Convert String to Datetime Oracle – using table

SELECT 
to_date(hiredate, 'DD/MM/YYYY') hiringdate
FROM test2;

Output:-

HIRINGDAT
---------
10-DEC-25
15-AUG-30

Also see:- Oracle Change Column Length Varchar2

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search