Oracle ORA-01850 Error: Causes and Solutions Complete Guide

ORA-01850: Hour Must Be Between 1 and 12

ORA-01850 is a common Oracle date conversion error that occurs when using the HH (or HH12) format mask — which represents a 12-hour clock — while providing an hour value outside the valid range of 1 to 12. This typically happens when 24-hour format time data (hours 0–23) is passed into a function like TO_DATE or TO_TIMESTAMP that expects 12-hour format. Understanding the difference between HH and HH24 format masks is the key to resolving this error quickly.

---

Top 3 Causes

1. Using HH Format Mask with 24-Hour Time Data

The HH format mask only accepts values from 1 to 12. Passing hours like 00, 13, or 23 will immediately trigger ORA-01850.

-- Causes ORA-01850: hour 14 is out of range for HH format
SELECT TO_DATE('2024-07-15 14:30:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

-- Fix: Use HH24 for 24-hour format
SELECT TO_DATE('2024-07-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Also works with TO_TIMESTAMP
SELECT TO_TIMESTAMP('2024-07-15 23:59:59', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2. Midnight (Hour = 0) Passed into HH12 Format

Some developers mistakenly use 00 for midnight when using the 12-hour clock format. In 12-hour notation, midnight must be expressed as 12:00:00 AM, not 00:00:00.

-- Causes ORA-01850: '00' is not valid for HH format
SELECT TO_DATE('2024-07-15 00:30:00 AM', 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;

-- Fix: Midnight must be 12 AM, not 00
SELECT TO_DATE('2024-07-15 12:30:00 AM', 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;
-- Result: 2024-07-15 00:30:00

-- Noon example
SELECT TO_DATE('2024-07-15 12:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;
-- Result: 2024-07-15 12:00:00

3. Implicit Conversion Relying on NLS_DATE_FORMAT with HH

When NLS_DATE_FORMAT is set to a format containing HH, any implicit string-to-date conversion with 24-hour time strings will fail intermittently — making this a particularly tricky bug to track down in production.

-- Check current NLS_DATE_FORMAT setting
SELECT VALUE 
FROM NLS_SESSION_PARAMETERS 
WHERE PARAMETER = 'NLS_DATE_FORMAT';

-- If it contains HH, this implicit conversion will fail:
-- INSERT INTO log_table (log_time) VALUES ('2024-07-15 22:00:00'); -- ORA-01850

-- Fix: Always use explicit format masks
INSERT INTO log_table (log_time) 
VALUES (TO_DATE('2024-07-15 22:00:00', 'YYYY-MM-DD HH24:MI:SS'));

-- Or change session-level format to a safe default
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

---

Quick Fix Summary

Scenario	Wrong	Correct
24-hour time	HH	HH24
Midnight	00:00:00 with HH	12:00:00 AM with HH AM
Implicit conversion	Rely on NLS	Always use TO_DATE with format

---

Prevention Tips

1. Standardize on HH24 across all codebases. Adopt a team-wide coding standard that mandates HH24 as the default format mask for all date/time conversions. Add a code review checklist item to flag any standalone use of HH without an accompanying AM/PM element.

2. Never rely on implicit date conversion. Always explicitly call TO_DATE() or TO_TIMESTAMP() with a hard-coded format mask. Implicit conversions that depend on NLS_DATE_FORMAT are a leading cause of environment-specific, hard-to-reproduce date errors in Oracle applications.
   

-- Best practice wrapper function for safe date conversion
CREATE OR REPLACE FUNCTION safe_to_date(p_date_str IN VARCHAR2) 
RETURN DATE IS
BEGIN
    RETURN TO_DATE(p_date_str, 'YYYY-MM-DD HH24:MI:SS');
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- Or raise a custom application error
END;
/

---

Related Errors

• ORA-01849 – Minutes must be between 0 and 59
• ORA-01851 – Minutes out of range
• ORA-01843 – Invalid month value
• ORA-01858 – Non-numeric character found where numeric expected in date string

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.