DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01861 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01861: Literal Does Not Match Format String

ORA-01861 is one of the most common Oracle date-handling errors, occurring when a string literal passed to a date conversion function doesn't match the specified format mask. For example, calling TO_DATE('2024/01/15', 'YYYY-MM-DD') will immediately throw this error because the slash delimiter in the value conflicts with the hyphen in the format string. Understanding this error and fixing it correctly is essential for any developer or DBA working with Oracle databases.

Top 3 Causes

1. Mismatched Delimiter or Format Mask in TO_DATE / TO_TIMESTAMP

The most frequent cause: the actual date string format differs from the format mask provided to TO_DATE or TO_TIMESTAMP.

-- ERROR: delimiter mismatch (slash vs hyphen)
SELECT TO_DATE('2024/01/15', 'YYYY-MM-DD') FROM DUAL;
-- ORA-01861: literal does not match format string

-- FIXED: match the delimiter exactly
SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM DUAL;

-- FIXED: correct format for hyphen-separated dates
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- FIXED: handling timestamps
SELECT TO_TIMESTAMP('2024-01-15 09:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

2. Implicit Date Conversion Relying on NLS_DATE_FORMAT

When you compare or insert a string into a DATE column without using an explicit conversion function, Oracle relies on the session's NLS_DATE_FORMAT setting. If the string doesn't match that setting, ORA-01861 is thrown.

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

-- RISKY: relies on NLS_DATE_FORMAT (may work in dev, fail in prod)
SELECT * FROM ORDERS WHERE ORDER_DATE = '2024-01-15';

-- SAFE: always use explicit TO_DATE with a format mask
SELECT * FROM ORDERS
WHERE ORDER_DATE = TO_DATE('2024-01-15', 'YYYY-MM-DD');

-- SAFE: use ANSI date literal (no format mask needed, always reliable)
SELECT * FROM ORDERS
WHERE ORDER_DATE >= DATE '2024-01-01'
  AND ORDER_DATE <  DATE '2024-02-01';
Enter fullscreen mode Exit fullscreen mode

3. External or User-Supplied Data with Inconsistent Formats

Batch jobs, ETL pipelines, and web forms often deliver dates in varying formats. Without pre-validation, these values will trigger ORA-01861 when passed directly to a conversion function.

-- Validate before converting (Oracle 12c+)
SELECT
    date_str,
    VALIDATE_CONVERSION(date_str AS DATE, 'YYYY-MM-DD') AS is_valid
FROM input_staging_table;

-- Safe INSERT: only process rows with valid format
INSERT INTO ORDERS (ORDER_ID, ORDER_DATE)
SELECT order_id, TO_DATE(date_str, 'YYYY-MM-DD')
FROM input_staging_table
WHERE VALIDATE_CONVERSION(date_str AS DATE, 'YYYY-MM-DD') = 1;

COMMIT;

-- Handle multiple incoming formats gracefully
SELECT
    CASE
        WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2}$')
            THEN TO_DATE(date_str, 'YYYY-MM-DD')
        WHEN REGEXP_LIKE(date_str, '^\d{2}/\d{2}/\d{4}$')
            THEN TO_DATE(date_str, 'MM/DD/YYYY')
        ELSE NULL
    END AS parsed_date
FROM input_staging_table;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always specify both the value and the matching format mask in TO_DATE / TO_TIMESTAMP.
  • Use ANSI date literals (DATE 'YYYY-MM-DD') when possible — they require no format mask and ignore NLS settings entirely.
  • Avoid implicit date conversions — never compare a raw string to a DATE column without wrapping it in TO_DATE.
  • Use VALIDATE_CONVERSION (Oracle 12c+) to safely pre-check date strings before bulk operations.

Prevention Tips

Adopt explicit conversion as a team coding standard.
Mandate that all SQL touching DATE or TIMESTAMP columns must use TO_DATE or TO_TIMESTAMP with an explicit format mask. Enforce this rule during code reviews to eliminate environment-dependent NLS failures before they reach production.

Align NLS settings across all environments and validate external data early.
Ensure NLS_DATE_FORMAT is identical in development, QA, and production. For any pipeline ingesting external date strings, add a validation step using VALIDATE_CONVERSION or regex checks to catch format mismatches at the data entry point rather than deep inside business logic.

Related Errors

  • ORA-01830 – Format string ended before the full date value was converted.
  • ORA-01843 – Invalid month value supplied in a date string.
  • ORA-01858 – A non-numeric character was found where a number was expected during date parsing.

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