ORA-01810 Error Message

Learn the cause and how to resolve the ORA-01810 error message in Oracle.

Description

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

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

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

Re-write the TO_DATE function so that you only use each format code once. Examples of format codes are:

Format Code	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
MM	Month (01-12; JAN = 01).
MON	Abbreviated name 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 year (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 past 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 people mistakenly use the MM format code to represent minutes, thus using the MM format for both the months and the minutes.

You could correct this SELECT statement as follows:

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