Back to cookbooks list Articles Cookbook

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.

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.

company_idcompany_nameregistration_date
1Decathlon5 Jan 1978
2Vestel21 Sep 1991
3White Chapel18 Oct 2017
4Silvercup4 Dec 2021
5JohnnyBros28 Aug 1984

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:

company_idcompany_nameregistration_date
1Decathlon05-JAN-78
2Vestel21-SEP-91
3White Chapel18-OCT-17
4Silvercup4-DEC-21
5JohnnyBros28-AUG-84

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.

Recommended courses:

Recommended articles:

See also: