How to resolve ORA-01818 Error Message in Oracle database

Learn the reason and how to resolve the ORA-01818 error message in Oracle.

Description

When you stumble upon an ORA-01818 error, the following error message will appear:

ORA-01818: HH24 precludes use of meridian indicator

Cause

You tried to reference a date cost the use of each the 24 hour indicator (hh24) and the meridian indicator (AM or PM).

Resolution

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

Option #1

Correct the date with the aid of using either the 24 hour indicator (hh24) or the meridian indicator (AM or PM).

For example, if you tried to execute the following statement:

SELECT TO_DATE('2004/11/24 14:00:00 PM', 'yyyy/mm/dd hh24:mi:ss AM')
FROM dual;

You would receive the following error message:

You ought to unravel this error by means of disposing of one of the hour indicators.

If you wished to use the 24 hour indicator, you ought to correct the SQL as follows:

SELECT TO_DATE('2004/11/24 14:00:00', 'yyyy/mm/dd hh24:mi:ss')
FROM dual;

If you wished to use the meridian indicator (AM or PM), you may want to correct the SQL as follows:

SELECT TO_DATE('2004/11/24 2:00:00 PM', 'yyyy/mm/dd hh:mi:ss PM')
FROM dual;

Learn more about the TO_DATE function.