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

19 comments:

  1. This was extremely helpful. I spent one whole day to find a solution to this same error on an APEX application page. Today only I could find this and this helped me to fix mine. Thank you loads.

    ReplyDelete
  2. wow, what a helpful post is it! thanks for this!
    Now I have a question... why do you think that it occurs with an Access macro attacking to Oracle data base? thanks!

    ReplyDelete
  3. I do not even know the way I finished up here, but I believed this put up used to be great.

    I do not recognise who you're however certainly you're going
    to a well-known blogger should you are not already.

    Cheers!

    ReplyDelete
  4. I wanted to make it a little less fattening.

    ReplyDelete
  5. i got one different solution to this ORA- error here .. http://www.moreajays.com/2020/06/ora-01830-date-format-picture-ends.html

    ReplyDelete
  6. Till 2020 it is working fine but for 2021 we need to use only 'yy' instead of 'yyyy' for year format

    ReplyDelete
  7. Develop oracle coding is most important for database managemnet system..
    This post provides proper difference between social science and social studies..
    I have one more topic of related with fashion visit here online fashion

    ReplyDelete
  8. I got such a useful stuff on your website that helps me a lot to gain information-Buy German Silver Earrings online.

    ReplyDelete
  9. ทางเข้าpg soft slots games มีเกมให้สำหรับทุกคน PG SLOT แล้วก็ทุกๆรสนิยม คณะทำงานของพวกเราได้ปรับปรุงฟีพบร์มากหลายแบบเพื่อตอบรับกับเกมเมอร์ทุกแบบเพื่อทุกคุณได้รับประสบการณ์ที่ดี

    ReplyDelete