How to resolve ORA-01810 Error Message in Oracle database

Learn the motive and how to get to the bottom of the ORA-01810 error message in Oracle.

Description

When you come across an ORA-01810 error, the following error message will appear:

ORA-01810: format code appears twice

Cause

You tried to use the TO_DATE function in a query, however you used a layout code twice in the date format.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write the TO_DATE feature so that you only use every format code once. Examples of layout codes are:

Format Code Explanation YEAR Year, spelled out YYYY 4-digit year MM Month (01-12; JAN = 01). MON Abbreviated title of month. MONTH Name of month, padded with blanks to length of 9 characters. D Day of week (1-7). DAY Name of day. DD Day of month (1-31). DDD Day of yr (1-366). DY Abbreviated name of day. HH Hour of day (1-12). HH12 Hour of day (1-12). HH24 Hour of day (0-23). MI Minute (0-59). SS Second (0-59). SSSSS Seconds previous midnight (0-86399).

For example, if you tried to execute the following SELECT statement:

SELECT TO_DATE('2004/12/14 4:29 PM', 'YYYY/MM/DD HH:MM PM' )
FROM dual;

You would receive the following error message:

Some humans mistakenly use the MM structure code to characterize minutes, consequently the usage of the MM structure for each the months and the minutes.

You may want to correct this SELECT assertion as follows:

SELECT TO_DATE('2004/12/14 4:29 PM', 'YYYY/MM/DD HH:MI PM' )
FROM dual;

Learn more about the TO_DATE function.