DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01851 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01851: minutes must be between 0 and 59 — Cause & Fix

ORA-01851 is thrown by Oracle when a minutes value falls outside the valid range of 0 to 59 in a date/time function or INTERVAL expression. It commonly surfaces in TO_TIMESTAMP, TO_DATE, TO_DSINTERVAL, and INTERVAL literals. This error frequently appears when application code dynamically builds time strings or passes unvalidated user input directly to Oracle date functions.

Top 3 Causes

1. Invalid INTERVAL Literal or TO_DSINTERVAL Call

Specifying a minutes value ≥ 60 inside an INTERVAL literal or TO_DSINTERVAL string triggers ORA-01851 immediately.

-- Causes ORA-01851
SELECT INTERVAL '0 00:90:00' DAY TO SECOND FROM DUAL;

-- Also causes ORA-01851
SELECT TO_DSINTERVAL('0 00:75:00') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. TO_TIMESTAMP / TO_DATE with Out-of-Range Minutes

Passing a time string where the minutes portion exceeds 59 to a conversion function will raise this error.

-- Causes ORA-01851
SELECT TO_TIMESTAMP('2024-06-15 08:75:30', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Causes ORA-01851
SELECT TO_DATE('15-JUN-2024 23:61', 'DD-MON-YYYY HH24:MI') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

3. Dynamically Built INTERVAL Strings with Un-normalized Minutes

When application code computes a total number of minutes and concatenates it directly into an INTERVAL string without converting hours and minutes separately, the result exceeds the valid range.

-- Simulating what bad application logic produces
-- Total minutes = 130 -> incorrectly placed as-is
DECLARE
  v_total_min NUMBER := 130;
  v_interval  INTERVAL DAY TO SECOND;
BEGIN
  -- WRONG: directly using total minutes (causes ORA-01851)
  v_interval := TO_DSINTERVAL('0 00:' || v_total_min || ':00');
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 — Use NUMTODSINTERVAL (Recommended)

Let Oracle handle normalization by passing total seconds or minutes to NUMTODSINTERVAL.

-- Safe: pass total minutes as seconds, Oracle normalizes automatically
SELECT NUMTODSINTERVAL(90 * 60, 'SECOND') FROM DUAL;
-- Result: +00 01:30:00.000000

SELECT NUMTODSINTERVAL(150, 'MINUTE') FROM DUAL;
-- Result: +00 02:30:00.000000
Enter fullscreen mode Exit fullscreen mode

Fix 2 — Normalize Hours and Minutes Before Building the String

DECLARE
  v_total_minutes NUMBER := 130;
  v_hours         NUMBER := TRUNC(v_total_minutes / 60);
  v_minutes       NUMBER := MOD(v_total_minutes, 60);
  v_interval      INTERVAL DAY TO SECOND;
BEGIN
  v_interval := TO_DSINTERVAL(
    '0 ' || LPAD(v_hours, 2, '0') || ':' || LPAD(v_minutes, 2, '0') || ':00'
  );
  DBMS_OUTPUT.PUT_LINE(v_interval); -- +00 02:10:00.000000
END;
/
Enter fullscreen mode Exit fullscreen mode

Fix 3 — Validate Input Before Passing to Date Functions

-- Pre-screen bad rows before batch processing
SELECT time_string
FROM   raw_import_data
WHERE  TO_NUMBER(SUBSTR(time_string, 15, 2)) NOT BETWEEN 0 AND 59;

-- Wrap conversion in an exception handler for resilient processing
DECLARE
  v_ts TIMESTAMP;
BEGIN
  BEGIN
    v_ts := TO_TIMESTAMP('2024-01-15 10:75:00', 'YYYY-MM-DD HH24:MI:SS');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Skipping invalid record: ' || SQLERRM);
  END;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Standardize on NUMTODSINTERVAL across your codebase.
Ban raw INTERVAL literals in coding guidelines and always use NUMTODSINTERVAL or NUMTOYMINTERVAL. These functions accept total units and normalize automatically, eliminating the risk entirely.

-- Preferred pattern in all application code
SELECT SYSDATE + NUMTODSINTERVAL(90, 'MINUTE') FROM DUAL; -- clean and safe
Enter fullscreen mode Exit fullscreen mode

2. Validate all external time data at the ingestion boundary.
Before loading data from files, APIs, or user input into Oracle, apply a validation step that checks hour (0–23), minute (0–59), and second (0–59) ranges. A shared PL/SQL utility function or a CHECK constraint on staging tables catches bad data early and prevents ORA-01851 from ever reaching production.

Related Errors

Error Code Description
ORA-01850 Hour must be between 0 and 23
ORA-01852 Seconds must be between 0 and 59
ORA-01843 Not a valid month
ORA-01847 Day of month out of range

These errors belong to the same family of datetime validation errors. If you see ORA-01851, audit the surrounding date/time logic for all related range violations at the same time.

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