DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01849 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01849: hour must be between 0 and 23 — Causes, Fixes & Prevention

ORA-01849 is thrown by Oracle when a date or timestamp conversion receives an hour value outside the valid range of 0 to 23. This error most commonly surfaces during TO_DATE or TO_TIMESTAMP function calls when the input string contains an invalid hour component. It is a data integrity error, not a system error, so the fix always lies in correcting the input data or the format mask.

Top 3 Causes

1. Invalid Hour Value in Input String

The most frequent cause is simply bad data — a string containing an hour value of 24 or greater being passed to a date conversion function.

-- This will raise ORA-01849
SELECT TO_DATE('2024-01-15 25:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Fix: correct the hour value
SELECT TO_DATE('2024-01-15 23:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Handle midnight edge case (24:00:00 → next day 00:00:00)
SELECT
  CASE
    WHEN SUBSTR(raw_col, 12, 2) = '24'
    THEN TO_DATE(SUBSTR(raw_col, 1, 10), 'YYYY-MM-DD') + 1
    ELSE TO_DATE(raw_col, 'YYYY-MM-DD HH24:MI:SS')
  END AS fixed_date
FROM staging_table;
Enter fullscreen mode Exit fullscreen mode

2. Wrong Format Mask — HH Instead of HH24

Oracle's HH format token represents 12-hour time (1–12), while HH24 handles 24-hour time (0–23). Using HH when the hour value exceeds 12 will trigger ORA-01849.

-- ORA-01849: HH cannot accept 15 (3 PM)
SELECT TO_DATE('2024-01-15 15:30:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

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

-- Fix 2: keep HH but add AM/PM designator
SELECT TO_DATE('2024-01-15 03:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;

-- Check your current session date format
SELECT VALUE FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';
Enter fullscreen mode Exit fullscreen mode

3. Application-Side Data Passing an Invalid Hour

Applications built in Java, Python, or other languages sometimes pass malformed datetime strings due to timezone conversion bugs or off-by-one logic errors at midnight boundaries.

-- Validate data before bulk load using a helper function
CREATE OR REPLACE FUNCTION is_valid_datetime(
  p_str IN VARCHAR2,
  p_fmt IN VARCHAR2
) RETURN NUMBER IS
  v_date DATE;
BEGIN
  v_date := TO_DATE(p_str, p_fmt);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN RETURN 0;
END;
/

-- Filter only valid rows during ETL
SELECT raw_datetime
FROM staging_table
WHERE is_valid_datetime(raw_datetime, 'YYYY-MM-DD HH24:MI:SS') = 1;

-- Catch and log ORA-01849 in PL/SQL
DECLARE
  v_date DATE;
BEGIN
  v_date := TO_DATE('2024-01-15 25:00:00', 'YYYY-MM-DD HH24:MI:SS');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1849 THEN
      DBMS_OUTPUT.PUT_LINE('Invalid hour detected: ' || SQLERRM);
    ELSE
      RAISE;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Scenario Fix
Input string has hour > 23 Correct the source data
Using HH for 24-hour time Replace with HH24
Midnight sent as 24:00:00 Add +1 day logic and reset hour to 00
Bulk data load failures Pre-validate with a helper function

Prevention Tips

Standardize on HH24 across all code. Establish a team convention to always use YYYY-MM-DD HH24:MI:SS as the default date format string. Enforce it at the session level with a logon trigger:

CREATE OR REPLACE TRIGGER set_nls_on_logon
AFTER LOGON ON DATABASE
BEGIN
  EXECUTE IMMEDIATE
    'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
END;
/
Enter fullscreen mode Exit fullscreen mode

Add a BEFORE INSERT trigger to reject bad data at the database boundary, so no invalid hour value ever reaches your tables regardless of what the application sends.

CREATE OR REPLACE TRIGGER trg_check_hour
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF TO_NUMBER(TO_CHAR(:NEW.order_time, 'HH24')) NOT BETWEEN 0 AND 23 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid hour value in order_time.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01850 — minute must be between 0 and 59
  • ORA-01851 — seconds must be between 0 and 59
  • ORA-01843 — not a valid month
  • ORA-01847 — day of month must be between 1 and last day of month

📖 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.

Top comments (0)