DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01858 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01858: A Non-Numeric Character Was Found Where a Numeric Was Expected

ORA-01858 is a date conversion error in Oracle that occurs when the database expects a numeric character during date string parsing but encounters a non-numeric character instead. It is most commonly triggered by a mismatch between the actual date string and the format mask provided to TO_DATE() or TO_TIMESTAMP() functions. Understanding the root cause is straightforward once you know where to look — it always comes down to a format inconsistency.

Top 3 Causes and Fixes

Cause 1: Mismatched Separator in Format Mask

The most frequent cause is a mismatch between the separator characters in the date string and the format mask.

-- WRONG: String uses '-' but format mask uses '/'
SELECT TO_DATE('2024-01-15', 'YYYY/MM/DD') FROM DUAL;
-- Result: ORA-01858

-- CORRECT: Match the separator exactly
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- Other valid examples
SELECT TO_DATE('20240115',   'YYYYMMDD')   FROM DUAL;
SELECT TO_DATE('2024.01.15', 'YYYY.MM.DD') FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Dirty or Unexpected Data in Date Columns

When data is imported from external sources (CSV, APIs, legacy systems), date columns may contain unexpected characters, leading spaces, or non-standard formats.

-- Check for problematic rows before converting
SELECT order_date
FROM   orders
WHERE  NOT REGEXP_LIKE(order_date, '^\d{4}[-/]\d{2}[-/]\d{2}$');

-- Use TRIM to remove leading/trailing spaces
SELECT TO_DATE(TRIM(order_date), 'YYYY-MM-DD')
FROM   orders;

-- Safe conversion using CASE + REGEXP validation
SELECT CASE
         WHEN REGEXP_LIKE(order_date, '^\d{4}-\d{2}-\d{2}$')
         THEN TO_DATE(order_date, 'YYYY-MM-DD')
         ELSE NULL
       END AS safe_order_date
FROM   orders;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Relying on Implicit Conversion with Wrong NLS_DATE_FORMAT

When no format mask is specified, Oracle falls back to the session's NLS_DATE_FORMAT. If that setting differs between environments (dev vs. prod), the same query can behave differently.

-- Check current session NLS setting
SELECT * FROM NLS_SESSION_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';

-- BAD: Implicit conversion — behavior depends on NLS settings
SELECT * FROM orders WHERE order_date = '2024-01-15';

-- GOOD: Always use an explicit format mask
SELECT * FROM orders
WHERE  order_date = TO_DATE('2024-01-15', 'YYYY-MM-DD');

-- Set session NLS if needed (temporary fix only)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Compare your string and format mask character by character — separators must match exactly.
  2. Always specify an explicit format mask in every TO_DATE() and TO_TIMESTAMP() call.
  3. Validate incoming data with REGEXP_LIKE before attempting conversion.
  4. Use native DATE/TIMESTAMP column types instead of VARCHAR2 for date storage.

Prevention Tips

  • Enforce explicit format masks as a coding standard. Never rely on implicit date conversion. Add this as a mandatory item in your code review checklist to prevent environment-dependent failures.
  • Define date columns as DATE or TIMESTAMP, never VARCHAR2. Combined with CHECK constraints and ETL-level data validation, this eliminates the root source of bad data that causes ORA-01858 at the database design level.

Related Oracle Errors

Error Code Description
ORA-01861 Literal does not match format string — closely related
ORA-01843 Invalid month value
ORA-01847 Invalid day of month value
ORA-01840 Input value not long enough for date format

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