CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Jacob
Jacob

Reputation: 14731

ORA-01858: Error when trying to insert into timestamp(0) column

I have data in below mentioned format, when I am trying to insert into table I am getting errors

ORA-01858: a non-numeric character was found where a numeric was expected

How can I successfully insert record into table?

create table mytab (dt timestamp(0));

Insert into mytab
   (dt)
 Values
   (TO_TIMESTAMP('16/JUL/13 2:53:08. PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'));

Upvotes: 0

Views: 1852

Answers (3)

APC
APC

Reputation: 146209

You need to specify zero fractional seconds. Here's your error:

SQL> create table mytab (dt timestamp(0));

Table created.

SQL> Insert into mytab
   (dt)
 Values
   (TO_TIMESTAMP('16/JUL/13 2:53:08. PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'));   2    3    4  
   (TO_TIMESTAMP('16/JUL/13 2:53:08. PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'))
                 *
ERROR at line 4:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL>

Let's fix the input 

SQL> ed
Wrote file afiedt.buf

  1  Insert into mytab
  2     (dt)
  3   Values
  4*    (TO_TIMESTAMP('16/JUL/13 2:53:08.00 PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'))
SQL> r
  1  Insert into mytab
  2     (dt)
  3   Values
  4*    (TO_TIMESTAMP('16/JUL/13 2:53:08.00 PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'))

1 row created.

SQL>

So even though you've specified a timestamp with a zero precision, your input still needs to match the mask. Which means you need to have .00 to match the .FF .

Alternatively, don't bother including the fractional seconds at all:

SQL> ed
Wrote file afiedt.buf

  1  insert into mytab
  2     (dt)
  3   Values
  4*    (TO_TIMESTAMP('16/JUL/13 2:53:08 PM','DD/MON/YY fmHH12fm:MI:SS AM'))
SQL> r
  1  insert into mytab
  2     (dt)
  3   Values
  4*    (TO_TIMESTAMP('16/JUL/13 2:53:08 PM','DD/MON/YY fmHH12fm:MI:SS AM'))

1 row created.

SQL>

Incidentally, note that a TIMESTAMP(0) will round any fractional seconds, rounding up at the half second. Whether that matters depends on how you'll be populating the column and how accurate the time needs to be.

Upvotes: 3

Oscar Pérez
Oscar Pérez

Reputation: 4397

You need the second fraction:

Insert into mytab(dt)
 Values
    (TO_TIMESTAMP('16/JUL/13 2:53:08.00 PM','DD/MON/YY fmHH12fm:MI:SS.FF AM'));

Upvotes: 2

slavoo
slavoo

Reputation: 6076

You have one "." after minutes

this works

select TO_TIMESTAMP('16/JUL/13 2:53:08 PM','DD/MON/YY fmHH12fm:MI:SS.FF AM') 
from dual

Upvotes: 1

Related Questions