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

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.
Action: Check the specifications for date format pictures and correct the statement.

ORA-01830 is a common oracle error, it usually occurs when date value is entered, date format does not match with the date value.

We can reproduce ORA-01830 in many ways, followings are very common:

SQL> DESC EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 HIREDATE                                           DATE
 
SQL> insert into emp values (101,'sachin','16-May-2007 09:54');
insert into emp values (101,'sachin','16-May-2007 09:54')
                                     *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

or we may simply reproduce it by simply using to_date

SQL> select to_date('16-May-2007 09:54') from dual;
select to_date('16-May-2007 09:54') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> select to_date('16-May-2007 09:54','dd-mon-yyyy') from dual;
select to_date('16-May-2007 09:54','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Solution:
To resolve ORA-01830, we need to simply use following tips as rules when working with conversion of string to date.
– TO_DATE must be used with FORMAT while converting string to date
– The FORMAT MUST match the string data.

So to resolve ORA-01830 issues we reproduced here we can simply modify our queries to

SQL> insert into emp values (101,'sachin',to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi'));
1 row created.

SQL> select to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi') from dual;
TO_DATE('
---------
16-MAY-07

SQL> select to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi:ss') from dual;
TO_DATE('
---------
16-MAY-07

One thing here to note is that in my last example data was ’16-May-2007 09:54′ and format was ‘dd-Mon-yyyy hh24:mi:ss’ and it worked even when there was no value for “:ss”. So ORA-01830 is raised only when string value contains extra than the format.