Oracle / PLSQL: 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:
- ORA-01810: format code appears twice
Cause
You tried to use the TO_DATE function in a query, but you used a format code twice in the date format.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
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;
Advertisements