ORA-01830 means TO_DATE or TO_TIMESTAMP finished reading the format model while there was still text left in the input string. The string carries more than the format describes, most often a time part after the date. Either extend the format to cover the whole string, or cut the string down to the part you want.
Reproduce the Error
Example:
select to_date('2026-03-15 14:30', 'YYYY-MM-DD') from dual;
select to_date('15-MAR-2026 08:00:00', 'DD-MON-YYYY HH24:MI') from dual;Output:
Error starting at line : 1
In command -
select to_date('2026-03-15 14:30', 'YYYY-MM-DD') from dual
Error at Command Line : 1 Column : 16
Error report -
SQL Error: ORA-01830: Date format picture ends before converting entire input string.
Error starting at line : 2
In command -
select to_date('15-MAR-2026 08:00:00', 'DD-MON-YYYY HH24:MI') from dual
Error at Command Line : 2 Column : 16
Error report -
SQL Error: ORA-01830: Date format picture ends before converting entire input string.Both strings contain a time, or more of it, than the format reads: 14:30 is left over in the first, and :00 seconds in the second.
Fix: Describe the Whole String, or Cut It
Example:
select to_char(to_date('2026-03-15 14:30', 'YYYY-MM-DD HH24:MI'), 'YYYY-MM-DD HH24:MI') as with_time
from dual;
-- keep only the date part of a longer string
select to_date(substr('2026-03-15 14:30', 1, 10), 'YYYY-MM-DD') as date_only from dual;Output:
WITH_TIME ___________________ 2026-03-15 14:30 DATE_ONLY ______________ 15-MAR-2026
TO_CHAR shows that the time was read. SUBSTR keeps only the date part when the time is not needed.
Things to Know
- Strings from other systems often carry seconds, fractions, or a time zone; use TO_TIMESTAMP or TO_TIMESTAMP_TZ with FF and TZH:TZM for those.
- When the format is longer than the string, Oracle is lenient: missing time parts become zero.
- DEFAULT NULL ON CONVERSION ERROR returns NULL instead of raising the error for strings that do not fit.
Related Guides
Conclusion
ORA-01830 means the input string is longer than its format model. Add the missing parts, usually the time, to the format, or take only the part of the string the format describes.
