In-depth Analysis of ORA-01810 Error: Duplicate Date Format Codes in Oracle and Solutions

Overview of ORA-01810 Error

In Oracle database operations, ORA-01810 is a common date format error with the full message "ORA-01810: format code appears twice". This error typically occurs when using TO_DATE or TO_TIMESTAMP functions with duplicate format codes in the format mask. Understanding the root cause is essential for writing correct SQL statements.

Case Study: The Problematic SQL Statement

Consider the following SQL INSERT statement attempting to insert data into the bag_grte_clm table:

INSERT INTO bag_grte_clm
(
    schd_dprt_ldt,
    arr_trpn_stn_cd,
    bkg_crtn_gdt,
    sbmt_bag_grte_clm_dt,
    bag_grte_clm_stt_cd,
    lst_updt_gts,
    bag_grte_clm_gts,
    dprt_trpn_stn_cd
)
VALUES (
    TO_DATE('2015/12/06', 'yyyy/mm/dd'),
    'YUL',
    TO_DATE('2015-11-15', 'yyyy-mm-dd'),
    TO_DATE('120615', 'MMDDYY'),
    'DENIAL',
    (current_timestamp),
    TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss'),
    'ATL'
)

Executing this statement triggers the ORA-01810 error. The key issue lies in the TO_TIMESTAMP function call: TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss').

Error Cause Analysis

This TO_TIMESTAMP call has two main problems:

1. Duplicate Format Code

In the format mask 'yyyymmdd hh:mm:ss', mm appears twice: once in yyyymmdd representing month, and again in hh:mm:ss where it should represent minutes. According to Oracle documentation, MM is the format code for month, while MI is for minutes. When Oracle parses the second mm, it detects that the month format code has already been used, throwing the ORA-01810 error.

Verifying this error is straightforward:

SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss') FROM dual;

This query directly returns the ORA-01810 error.

2. Incorrect Time Format

Even after fixing the duplicate format code, the time portion 00:00:00 remains problematic. When using the 12-hour format hh, the hour value must be between 1 and 12. Thus, 00:00:00 causes ORA-01849 error: "hour must be between 1 and 12".

Testing this error:

SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mi:ss') FROM dual;

This returns the ORA-01849 error.

Solutions

Several solutions address these issues:

Solution 1: Use 24-Hour Format

Change the hour format code to HH24, which allows hour values from 0-23:

SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh24:mi:ss') my_tmstamp FROM dual;

Result: 06-DEC-15 12.00.00.000000000 AM

Solution 2: Omit Time Portion

If precise time is not needed, provide only the date portion:

SELECT TO_TIMESTAMP('20151206', 'yyyymmdd') my_tmstamp FROM dual;

Result: 06-DEC-15 12.00.00.000000000 AM

In this case, Oracle defaults the time to midnight (12:00:00 AM).

Solution 3: Use Correct 12-Hour Time

If 12-hour format is required, provide a valid hour value (1-12):

SELECT TO_TIMESTAMP('20151206 12:00:00', 'yyyymmdd hh:mi:ss') FROM dual;

This represents 12:00 noon.

Format Code Details

Understanding Oracle date format codes is key to avoiding such errors:

• YYYY or YY: Four-digit or two-digit year
• MM: Month (01-12)
• DD: Day (01-31)
• HH or HH12: 12-hour hour (01-12)
• HH24: 24-hour hour (00-23)
• MI: Minutes (00-59)
• SS: Seconds (00-59)

Note: MM and MI may look similar but represent entirely different time units. This subtle distinction is a common pitfall for developers.

Supplementary Insights from Other Answers

Beyond the primary answer, other responses offer valuable insights. For instance, one suggests referring to Oracle official documentation on date format models, providing direction for deeper learning. However, these answers often lack specific error analysis and solutions, hence their lower scores.

Best Practices Recommendations

To avoid ORA-01810 and other date format errors, consider:

1. Always use explicit format codes to prevent confusion from abbreviations
2. Establish uniform date format standards within teams
3. Use 24-hour format to avoid 12-hour time range limitations
4. Test date conversion parts separately in complex queries
5. Refer to Oracle official documentation for all available format codes

Conclusion

The ORA-01810 error, while seemingly simple, highlights important details in Oracle date handling. By correctly understanding and using format codes, especially distinguishing between MM and MI, developers can avoid such errors. The solutions provided here not only fix the specific example but also offer general methods for various date format scenarios. In practice, combining 24-hour format with clear format mask design significantly enhances code reliability and maintainability.