This discussion is archived
4 Replies Latest reply: Dec 2, 2010 1:59 AM by Hoek RSS

ORA-01810: format code appears twice

755443 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks BluShadow!!

    Regards,
    dbajug
  • 4. Re: ORA-01810: format code appears twice
    Hoek Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points