How to Get the Date From a String in Oracle

Problem

You’d like to convert a string to a date in your result set in Oracle database.

Example

Our database has a table named accounts_creation, with a field named company_id of the data type NUMBER and two NVARCHAR2 fields named company_name and registration_date as shown below.

We want to convert the string column registration_date into a date column.

Solution

We’ll use the TO_DATE() function. Here’s the query:

SELECT 
  company_id,
  company_name,
  TO_DATE(registration_date,'DD Mon YEAR') AS registration_date
FROM accounts_creation;

Here’s the result of the query:

Discussion

The TO_DATE(string, format) function in Oracle converts a string value to a date. The function takes two arguments: the string to convert and the date format. In the format specification, DD signifies the day of the month (1-31), MON signifies the abbreviated name of the month, and YEAR signifies the 4-digit year. You can read more about the available formats in the Oracle SQL documentation here. Here are the most commonly used formats: