ORA-01830: date format picture ends before converting entire input string

ORA-01830: date format picture ends before converting entire input string

Oracle PL/SQL error message: ORA-01830: date format picture ends before converting entire input string.

Cause:

A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.

Solution:

Check the specifications for date format pictures and correct the statement.

Example 1:

SELECT TO_DATE('25-DEC-2015 10:123', 'DD-MON-YYYY HH24:MI' ) FROM dual;

Output:

ORA-01830: date format picture ends before converting entire input string

Correct:

SELECT TO_DATE('25-DEC-2015 10:12', 'DD-MON-YYYY HH24:MI' ) FROM dual;

Output:

25-DEC-2015 10:12:00

Example 2:

CREATE TABLE TEST4(
ID NUMBER, 
NAME VARCHAR2(50),
INSERT_DATE DATE
);

Insert into TEST4 (ID, NAME, INSERT_DATE) 
values (1,'hello', '29-DEC-2015 3:23:09 PM');

Output:

table TEST4 created.
ORA-01830: date format picture ends before converting entire input string

Correct:

Insert into TEST4 (ID, NAME, INSERT_DATE) values 
(1,'hello', to_date('29-DEC-2015 3:23:09 PM','DD-MON-YYYY HH:MI:SS PM'));

Output:

1 rows inserted.