Oracle 11g r2: Cannot create partition on timestamp field

Question

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?

• Phil Sumner: Check your date format. The default date format depends on NLS_TERRITORY & can be overriden by other settings, for example NLS_DATE_FORMAT.

Answer 1

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.