ORA-01810: format code appears twice

Error Message

ORA-01810: format code appears twice

Cause of Error

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

Resolution

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;