I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this.

CREATE TABLE t (id NUMBER , tstamp timestamp )
  PARTITION BY RANGE (tstamp)  (
    PARTITION t_jan_2009 VALUES LESS THAN (to_timestamp('2009-02-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2')),
    PARTITION t_feb_2009 VALUES LESS THAN (to_timestamp('2009-03-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2'))

INSERT INTO t SELECT 1, '2009-02-10 12:34:45.56' from dual;

This is not the actual data but an equivalent sample. It gives the same error. SQL Error: ORA-01843: not a valid month

Why am I getting this error? Do I need to do something more?

  • Check your date format. The default date format depends on NLS_TERRITORY & can be overriden by other settings, for example NLS_DATE_FORMAT. Oct 13 '14 at 13:07

change to

insert into t select 1, to_timestamp ('2009-02-10 12:34:45.56','YYYY-MM-DD HH24:MI:SS.FF2') from dual;

should work. Don't rely on default formats.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.