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:
- 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
to12
range if you are using the 'HH' or 'HH12' format code. - Correct the Format Mask: If your input data is in a 24-hour format (which allows for hours from
00
to23
), you should use the 'HH24' format code in yourTO_DATE
orTO_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.