DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01830 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01830: date format picture ends before converting entire input string

ORA-01830 is one of the most common date conversion errors in Oracle. It occurs when you use TO_DATE() or TO_TIMESTAMP() and the format mask you provide is shorter than the input string — Oracle successfully parses up to the end of the format mask, but finds there are still unprocessed characters remaining in the input. In short, your format picture and your input string are mismatched in length or structure.

Top 3 Causes

1. Input string contains time info, but format mask is date-only

This is by far the most frequent cause. You pass a datetime string like '2024-01-15 14:30:00' but only specify 'YYYY-MM-DD' as the format mask. Oracle parses the date portion successfully, then sees 14:30:00 still remaining and throws ORA-01830.

-- Error example
SELECT TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD') FROM DUAL;
-- ORA-01830: date format picture ends before converting entire input string

-- Fix 1: Extend the format mask to include time
SELECT TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Fix 2: Trim the string to date part only
SELECT TO_DATE(SUBSTR('2024-01-15 14:30:00', 1, 10), 'YYYY-MM-DD') FROM DUAL;

-- Fix 3: Use TO_TIMESTAMP for full datetime precision
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. Format mask structure doesn't match the input string format

When input strings are compact (no delimiters), like '20240115143000', using a mask like 'YYYY-MM-DD' causes a mismatch. Oracle reads 2024, then expects a - but the format mask ends before consuming the full 14-character string.

-- Error example
SELECT TO_DATE('20240115143000', 'YYYY-MM-DD') FROM DUAL;
-- ORA-01830 occurs

-- Fix: Match the format mask exactly to the input structure
SELECT TO_DATE('20240115143000', 'YYYYMMDDHH24MISS') FROM DUAL;

-- Fix: Extract only the date portion
SELECT TO_DATE(SUBSTR('20240115143000', 1, 8), 'YYYYMMDD') FROM DUAL;

-- Practical bulk conversion example
SELECT
    employee_id,
    TO_DATE(SUBSTR(hire_date_raw, 1, 8), 'YYYYMMDD') AS hire_date
FROM employees_staging;
Enter fullscreen mode Exit fullscreen mode

3. Implicit conversion relying on NLS_DATE_FORMAT

When you insert or compare a string against a DATE column without an explicit TO_DATE() call, Oracle relies on the session's NLS_DATE_FORMAT setting. If your string contains time information but NLS_DATE_FORMAT is set to 'DD-MON-RR' or 'YYYY-MM-DD', ORA-01830 will fire. This is especially dangerous because it works fine in one environment and breaks silently in another.

-- Risky implicit conversion
INSERT INTO orders (order_date) VALUES ('2024-01-15 14:30:00');
-- May raise ORA-01830 depending on NLS_DATE_FORMAT

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

-- Always use explicit conversion
INSERT INTO orders (order_date)
VALUES (TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));

-- Explicit conversion in WHERE clause (also prevents index skip issues)
SELECT * FROM orders
WHERE order_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
  AND order_date <  TO_DATE('2024-02-01', 'YYYY-MM-DD');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Scenario Solution
Datetime string, date-only mask Add HH24:MI:SS to format mask
No-delimiter string Remove delimiters from format mask
Implicit conversion issue Always use explicit TO_DATE() with format mask
Unknown input format Use SUBSTR + TRIM to normalize before converting

Prevention Tips

Always specify an explicit format mask. Never rely on NLS_DATE_FORMAT for production code. Every TO_DATE() call should include a hardcoded format mask that matches the exact structure of the input data. Treat any date conversion without an explicit format mask as a code smell during review.

-- Bad practice
INSERT INTO log_table (event_date) VALUES ('2024-01-15 09:00:00');

-- Good practice
INSERT INTO log_table (event_date)
VALUES (TO_DATE('2024-01-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
Enter fullscreen mode Exit fullscreen mode

Validate input format before conversion. When consuming data from external sources, use REGEXP_LIKE to validate the string format before attempting conversion. This prevents ORA-01830 and related errors from surfacing at runtime.

-- Validate before converting
SELECT
    raw_date,
    CASE
        WHEN REGEXP_LIKE(raw_date, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$')
        THEN TO_DATE(raw_date, 'YYYY-MM-DD HH24:MI:SS')
        ELSE NULL
    END AS clean_date
FROM staging_table;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01861 – literal does not match format string: The format mask structure itself doesn't match the input (e.g., wrong delimiters).
  • ORA-01843 – not a valid month: Month value is invalid, often tied to NLS_DATE_LANGUAGE mismatches.
  • ORA-01858 – a non-numeric character was found where a numeric was expected: Non-digit found where a number was required in the 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.

Top comments (0)