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,'Nimish','16-May-2007 09:54');
insert into emp values (101,'Nimish','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,'Nimish',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.


Related Posts:
- Why to Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Date Difference in Days, Months and Years
- Dates Difference in days, hours, minutes & seconds
- ORA-01403: no data found