Oracle Linux

What is TO_DATE in Oracle and How to Use it?

Developers often face an issue while developing applications that need date values but they are mostly stored as strings in databases or files. To convert these string values into date values Oracle offers the TO_DATE function. This function supports numerous date formats which makes it an essential tool for developers.

This post will discuss the following content:

What is TO_DATE in Oracle?

In Oracle, the “TO_DATE” function is a common function utilized to convert string values with CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types into date values. It is possible through the numerous supported date formats, such as numeric, alphanumeric, and date literals. Let us see some data formats and their explanation:

Data Formats Explanation
YYYY Four digits of the year
YY Last two digits of the year
MM Month (01 equals January)
MON Three characters abbreviation of the month name
DD Number of the day of the month

This function aids developers in situations where the date is stored as a string in a database or file, and they want to convert it to a date value for their applications. It is supported by many versions of Oracle databases such as Oracle 12c and Oracle 9i.

How to Use TO_DATE in Oracle?

Login to your database using SQL PLUS or SQL Developer and use this syntax provided below to use the “TO_DATE” function in Oracle:

TO_DATE(string, format_elements)

Here, the “string” is the string that users want to convert to a date, and the “format_elements” is a string that specifies the format of the date.

Let us see some examples of using the Oracle TO_DATE function.

Example 1: Convert String to Date

Here, converts a simple string in the data value using this command:

SELECT TO_DATE('1999-12-25', 'yyyy-mm-dd')"DATE" FROM dual;

The above command converts the “1999-12-25” string to the date value in the provided format.

Output

The output depicted the converted date value after executing the command successfully.

Note: The format parameter in the “TO_DATE” function should match the format of the string being converted, otherwise an error can prompt.

Example 2: Convert String to Date Using the Default Format

The TO_DATE function can also be used to convert the string to the date value using the default format of the system. Let us run the command given below to do this:

SELECT TO_DATE('19-APRIL-2023') "Default_Format_Date" FROM dual;
150000

The command converts the “19-APRIL-2023” string to the date value in the provided format.

Output

The output depicted the converted date value in a column named “Default_Format_Date“.

Conclusion

The “TO_DATE” function in Oracle converts string values with CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types into date values using a variety of supported date formats. It aids developers that are working with date values stored as strings in databases or files. It is essential to ensure that the format parameter used in the function matches the format of the string. This post discussed the TO_DATE function in Oracle and its use.

About the author

Nimrah Ch

I’m a technical writer with a Bachelor's in Software Engineering and Certification in Cyber Security. Through my research and writing, I strive to provide readers with in-depth, informative articles that can help them make informed decisions about their technological needs.