Tuesday, December 3, 2013

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 CodeExplanation
YEARYear, spelled out
YYYY4-digit year
MMMonth (01-12; JAN = 01).
MONAbbreviated name of month.
MONTHName of month, padded with blanks to length of 9 characters.
DDay of week (1-7).
DAYName of day.
DDDay of month (1-31).
DDDDay of year (1-366).
DYAbbreviated name of day.
HHHour of day (1-12).
HH12Hour of day (1-12).
HH24Hour of day (0-23).
MIMinute (0-59).
SSSecond (0-59).
SSSSSSeconds 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;