DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01852 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01852: seconds must be between 0 and 59

ORA-01852 is an Oracle database error that occurs when a seconds value provided in a date or timestamp expression falls outside the valid range of 0 to 59. This typically happens during date conversion functions like TO_DATE or TO_TIMESTAMP, or when inserting/updating date values with an invalid seconds component. It is a common error in ETL pipelines, data migrations, and applications that process date strings from external systems without proper validation.

Top 3 Causes

1. Invalid Seconds Value in TO_DATE / TO_TIMESTAMP

Passing a seconds value of 60 or greater directly into a date conversion function is the most frequent trigger.

-- This will raise ORA-01852
SELECT TO_DATE('2024-06-01 12:00:60', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- This will also raise ORA-01852
SELECT TO_TIMESTAMP('2024-06-01 08:15:61.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

-- Correct usage
SELECT TO_DATE('2024-06-01 12:00:59', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('2024-06-01 08:15:59.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. Dirty Data from External Systems or Migrations

When migrating data from MySQL, MSSQL, or legacy systems, records with invalid seconds values can slip through without proper cleansing.

-- Identify records with invalid seconds in a staging table
SELECT raw_date_str,
       TO_NUMBER(SUBSTR(raw_date_str, 18, 2)) AS sec_value
FROM   staging_table
WHERE  TO_NUMBER(SUBSTR(raw_date_str, 18, 2)) NOT BETWEEN 0 AND 59;

-- Cleanse and insert with corrected seconds
INSERT INTO target_table (event_date)
SELECT
    CASE
        WHEN TO_NUMBER(SUBSTR(raw_date_str, 18, 2)) BETWEEN 0 AND 59
            THEN TO_DATE(raw_date_str, 'YYYY-MM-DD HH24:MI:SS')
        ELSE
            TO_DATE(SUBSTR(raw_date_str, 1, 16) || ':59',
                    'YYYY-MM-DD HH24:MI:SS')
    END
FROM staging_table;
Enter fullscreen mode Exit fullscreen mode

3. Insufficient Validation of User Input or Dynamic SQL

Applications that construct date strings dynamically—from user input or calculated values—without validating the seconds component can easily produce out-of-range values.

-- Safe PL/SQL wrapper to avoid ORA-01852
CREATE OR REPLACE FUNCTION safe_to_date(
    p_str    IN VARCHAR2,
    p_fmt    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS'
) RETURN DATE IS
    v_sec  NUMBER;
    v_date DATE;
BEGIN
    v_sec := TO_NUMBER(SUBSTR(p_str, INSTR(p_str, ':', 1, 2) + 1, 2));
    IF v_sec NOT BETWEEN 0 AND 59 THEN
        -- Clamp to 59 or raise a custom application error
        RAISE_APPLICATION_ERROR(-20001,
            'Invalid seconds value: ' || v_sec);
    END IF;
    v_date := TO_DATE(p_str, p_fmt);
    RETURN v_date;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END safe_to_date;
/

-- Usage
SELECT safe_to_date('2024-06-01 10:30:45') FROM DUAL; -- OK
SELECT safe_to_date('2024-06-01 10:30:61') FROM DUAL; -- Returns NULL + logs error
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Fix 1: Correct the seconds value directly
SELECT TO_DATE('2024-06-01 10:30:59', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Fix 2: If seconds = 60 means "next minute", add an interval instead
SELECT TO_DATE('2024-06-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS')
       + INTERVAL '1' MINUTE AS fixed_date
FROM DUAL;

-- Fix 3: Use REGEXP to pre-filter invalid rows before bulk load
SELECT *
FROM   staging_table
WHERE  NOT REGEXP_LIKE(raw_date_str,
       '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:[0-5][0-9]$');
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Add a CHECK constraint or BEFORE INSERT trigger
Enforce seconds range validation at the database level so no invalid data can be committed, regardless of the application layer.

-- Trigger-based prevention
CREATE OR REPLACE TRIGGER trg_check_seconds
BEFORE INSERT OR UPDATE ON event_log
FOR EACH ROW
BEGIN
    IF TO_NUMBER(TO_CHAR(:NEW.event_date, 'SS')) NOT BETWEEN 0 AND 59 THEN
        RAISE_APPLICATION_ERROR(-20052,
            'Seconds must be between 0 and 59.');
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Always validate date strings before ETL loads
Add a mandatory pre-validation step in every ETL or batch job that checks all time components (HH, MI, SS) before attempting to load data into Oracle. Reject and quarantine any rows that fail validation rather than letting them cause runtime errors in production.

Related Errors

Error Code Description
ORA-01850 hour must be between 0 and 23
ORA-01851 minutes 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)