Oracle Database Error:
ORA-01830

What does this error mean?

The date format mask used in TO_DATE or TO_TIMESTAMP does not match the full length of the input string.

Common causes

• 1TO_DATE format mask is shorter than the input string (e.g. 'YYYY-MM-DD' for '2024-01-15 12:00:00')
• 2Source date string includes time component that the format mask does not cover
• 3ADF sends ISO 8601 datetime strings (with T separator) to an Oracle format mask that does not include it
• 4Extra whitespace or timezone offset in the string not accounted for in the format

How to fix it

1. 1Step 1: Match the format mask to the full input string: use `YYYY-MM-DD HH24:MI:SS` for datetime strings.
2. 2Step 2: For ISO 8601 strings with T: `TO_TIMESTAMP(col, 'YYYY-MM-DD"T"HH24:MI:SS')`.
3. 3Step 3: Use TO_TIMESTAMP_TZ for strings with timezone: `TO_TIMESTAMP_TZ(col, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')`.
4. 4Step 4: Strip the time component if only the date is needed: `SUBSTR(col, 1, 10)` then `TO_DATE(SUBSTR(col,1,10), 'YYYY-MM-DD')`.
5. 5Step 5: Check Oracle's NLS_DATE_FORMAT session parameter and set it explicitly in the pipeline session.

Example log output

ORA-01830: date format picture ends before converting entire input string
SQL: TO_DATE('2024-01-15 12:30:00', 'YYYY-MM-DD')

Frequently asked questions

Other data errors