Oracle TO_DATE Function

Summary: In this tutorial, you’ll learn how to use the Oracle TO_DATE() function to convert a date string to a value of the DATE type using a specific format.

Introduction to the Oracle TO_DATE function #

The TO_DATE() function converts a date string to a value of the DATE type using a custom format.

Here’s the basic syntax of the TO_DATE() function:

TO_DATE (string, format [, nls_language])Code language: SQL (Structured Query Language) (sql)

The TO_DATE() function has three parameters:

• string is a date string you want to convert to a DATE value. It can be a value of any data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
• format is the date and time format of the string. The format parameter is optional. If you omit it, the string must be in the standard date format, DD-MON-YY such as 31-DEC-2000. Note that if format is J, which is for Julian, then the string must be an integer. Here is the detailed information on how to construct the date format.
• nls_language is an expression that specifies the language for day and month names in the input date string. The nls_language is optional. If you omit it, the TO_DATE() function will use the default language of your session.

The TO_DATE() function returns a DATE value that corresponds to the input string.

If the input date string is NULL or blank, the TO_DATE() function returns NULL.

Converting a standard date string #

To convert a date string to a date, you use the date format elements such as YYYY for the 4-digit year, MM for the 2-digit month, DD for a 2-digit day.

The following statement uses the TO_DATE() function to convert a date string to a DATE value:

SELECT TO_DATE('2025-05-05', 'YYYY-MM-DD') 
FROM dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
05-MAY-25Code language: SQL (Structured Query Language) (sql)

Converting a date with time #

The following statement uses the TO_DATE() function to convert a date string that includes time to a date value:

SELECT TO_DATE('2025-05-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS') 
FROM dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
05-MAY-25Code language: SQL (Structured Query Language) (sql)

To display both the date and time of a DATE value, you can change the default format by executing the following statement:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';Code language: SQL (Structured Query Language) (sql)

If you execute the following query again, you’ll see both date and time in the output:

SELECT TO_DATE('2025-05-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS') result
FROM dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT             
-------------------
2025-05-05 14:30:00Code language: SQL (Structured Query Language) (sql)

Converting date strings with months #

The following statement converts a date string that has months as words to a DATE value:

SELECT TO_DATE('May 5, 2025', 'Month DD, YYYY') 
FROM dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT             
-------------------
2025-05-05 00:00:00Code language: SQL (Structured Query Language) (sql)

If you don’t specify the correct format, the TO_DATE() function will issue an error. For example:

SELECT TO_DATE('20250505', 'YYYYMD') result 
FROM dual;Code language: SQL (Structured Query Language) (sql)

Output:

ORA-01821: date format not recognizedCode language: SQL (Structured Query Language) (sql)

Inserting dates into a table #

First, create a table called users:

CREATE TABLE users (
  user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2 (50) NOT NULL,
  last_name VARCHAR2 (50) NOT NULL,
  joined_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert a new row into the  users table:

INSERT INTO
  users (first_name, last_name, joined_date)
VALUES
  (
    'Laureen',
    'Davidson',
    TO_DATE ('Feb 01 2017', 'Mon DD YYYY')
  );Code language: SQL (Structured Query Language) (sql)

Try it

Since Feb 01 2017 is not Oracle’s standard date format; we have to use the TO_DATE() function to convert it to a DATE value before inserting it into the users table.

Third, insert another row into the users table:

INSERT INTO
  users (first_name, last_name, joined_date)
VALUES
  (
    'Thomas',
    'Phelps',
    TO_DATE ('15 March 2017', 'DD Month YYYY')
  );Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we also use the TO_DATE() function to convert the date string '15 March 2017' to a date value using the format 'DD Month YYYY'.

Finally, check the data of the users table:

SELECT * FROM users;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Querying data with the TO_DATE function #

The following statement finds the users who joined on March 15, 2017 by using the TO_DATE function in the WHERE clause:

SELECT
  user_id,
  first_name,
  last_name,
  joined_date
FROM
  users
WHERE
  joined_date = TO_DATE ('2017-03-15', 'YYYY-MM-DD');Code language: SQL (Structured Query Language) (sql)

Try it

Using NLS language for localized date strings #

You can use the option if your data has date strings in other languages, such as French. For example:

SELECT
  TO_DATE (
    '05-Mai-2025',
    'DD-Mon-YYYY',
    'NLS_DATE_LANGUAGE=FRENCH'
  )
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
05-MAY-25Code language: SQL (Structured Query Language) (sql)

In this example, the month name Mai is in French. Therefore, we use the third argument to instruct the TO_DATE function to interpret it as May in English:

'NLS_DATE_LANGUAGE=FRENCH'Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the TO_DATE() function to convert a date string into a DATE value.
• The default date format depends on the NLS_DATE_FORMAT.