What causes ORA-01858?

Date string format does not match the TO_DATE format mask (e.g. 'Jan 15' when mask is 'MM DD'). Source date uses dashes (-) but format mask expects slashes (/). Source column contains mixed formats (some rows ISO, some DD/MM/YYYY). Null or empty string being converted to a date type

How do I fix ORA-01858?

Step 1: Align the format mask with the actual date string format.. Step 2: Use REGEXP_LIKE to validate date strings before conversion.. Step 3: Use CASE WHEN to handle mixed formats: `CASE WHEN REGEXP_LIKE(col, '^\d{4}-\d{2}-\d{2}$') THEN TO_DATE(col, 'YYYY-MM-DD') ELSE NULL END`.. Step 4: Pre-process dates in ADF expressions to normalize to a single format.. Step 5: Add a data quality check step before the Oracle load.

Medium severitydata

Oracle Database Error:
ORA-01858

What does this error mean?

Oracle expected a numeric value at a certain position of a date or number string but found a non-numeric character.

Common causes

1Date string format does not match the TO_DATE format mask (e.g. 'Jan 15' when mask is 'MM DD')
2Source date uses dashes (-) but format mask expects slashes (/)
3Source column contains mixed formats (some rows ISO, some DD/MM/YYYY)
4Null or empty string being converted to a date type

How to fix it

1Step 1: Align the format mask with the actual date string format.
2Step 2: Use REGEXP_LIKE to validate date strings before conversion.
3Step 3: Use CASE WHEN to handle mixed formats: `CASE WHEN REGEXP_LIKE(col, '^\d{4}-\d{2}-\d{2}$') THEN TO_DATE(col, 'YYYY-MM-DD') ELSE NULL END`.
4Step 4: Pre-process dates in ADF expressions to normalize to a single format.
5Step 5: Add a data quality check step before the Oracle load.

Example log output

ORA-01858: a non-numeric character was found where a numeric was expected
SQL: TO_DATE('2024/01/15', 'YYYY-MM-DD')

Frequently asked questions

How is ORA-01858 different from ORA-01843?

ORA-01843 is about an invalid month name/abbreviation; ORA-01858 is about finding a non-numeric character where Oracle expected a digit in the date format. Both indicate a format mask mismatch.

Source · docs.oracle.com/error-help/db/ora-01858