Resolving: ORA-01849 Hour Must Be Between 1 and 12 in Oracle

Resolving: ORA-01849 Hour Must Be Between 1 and 12 in Oracle

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

The Oracle error ORA-01849: hour must be between 1 and 12 occurs when you are dealing with date and time values, and Oracle expects a 12-hour format but receives an hour value outside this range. This error is common when using the TO_DATE or TO_TIMESTAMP functions with a format mask that specifies a 12-hour clock (using 'HH' or 'HH12') but the provided hour is not in the range of 1 to 12.

Here's how to resolve this error:

  1. Check Your Date Input: Review the date string that you are trying to convert to a date or timestamp. Ensure that the hour part is within the 1 to 12 range if you are using the 'HH' or 'HH12' format code.
  2. Correct the Format Mask: If your input data is in a 24-hour format (which allows for hours from 00 to 23), you should use the 'HH24' format code in your TO_DATE or TO_TIMESTAMP function instead of 'HH' or 'HH12'.

Here is an example illustrating both a problematic call and its resolution:

Problematic Call:

SELECT TO_DATE('2024-01-03 15:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

In the above SQL statement, the 'HH' format expects the hour to be in the range 1-12, but 15 is provided (which is a 24-hour format). So it will raise the error:

ORA-01849: hour must be between 1 and 12

Resolution:

SELECT TO_DATE('2024-01-03 15:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

By using 'HH24' instead of 'HH', the function correctly interprets the 15 as a valid hour in the 24-hour format.

To prevent such errors (ORA-01849: hour must be between 1 and 12), always ensure the format mask matches the actual data you're working with. If the input data source is variable or unknown, you may need to implement validation logic to check and potentially convert input times to the expected format before attempting to use them in SQL functions.