4 Replies Latest reply: Dec 2, 2010 3:59 AM by Hoek RSS

    ORA-01810: format code appears twice

    755443
      Hi all,

      I'm doing an insert statement and one of the values is a timestamp column.

      I put to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS.FFFFFF') and appears the error ORA-01810

      What is wrong?

      Regards,
      dbajug
        • 1. Re: ORA-01810: format code appears twice
          BluShadow
          You only need to specify FF once...

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions193.htm#SQLRF06142
          • 2. Re: ORA-01810: format code appears twice
            BluShadow
            Example:
            SQL> select to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS,FF') from dual;
            
            TO_TIMESTAMP('10/11/1010:42:06,309000','DD/MM/YYHH24:MI:SS,FF')
            ---------------------------------------------------------------------------
            10-NOV-10 10.42.06.309000000
            
            SQL>
            • 3. Re: ORA-01810: format code appears twice
              755443
              Thanks BluShadow!!

              Regards,
              dbajug
              • 4. Re: ORA-01810: format code appears twice
                Hoek
                What is wrong?
                The comma (depending on your NLS settings) and use FF:
                SQL> select to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS.FFFFFF') from dual;
                select to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS.FFFFFF') from dual
                                                               *
                ERROR at line 1:
                ORA-01810: format code appears twice
                
                
                SQL> select to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS.FF') from dual;
                select to_timestamp('10/11/10 10:42:06,309000','dd/mm/yy HH24:MI:SS.FF') from dual
                                    *
                ERROR at line 1:
                ORA-01830: date format picture ends before converting entire input string
                
                
                SQL> select to_timestamp('10/11/10 10:42:06.309000','dd/mm/yy HH24:MI:SS.FF') from dual;
                
                TO_TIMESTAMP('10/11/1010:42:06.309000','DD/MM/YYHH24:MI:SS.FF')
                ---------------------------------------------------------------------------
                10-11-10 10:42:06,309000000
                
                1 row selected.