Search code examples
sqloraclederby

how do I insert an Oracle TIMESTAMP into a Derby TIMESTAMP

So I have some Oracle TIMESTAMP in a SQL dump from my Oracle database. And I want to import that into the Derby database. In Oracle, the SQL statement is looking like:

Insert into TABLE_NAME (COL1, COL2, COL3) 
values (
  'blah',
  to_timestamp('17-MAR-11 15.52.25.000000000','DD-MON-RR HH24.MI.SS.FF'),
  'blah'
);

COL2 has type TIMESTAMP here. When I run it in Derby, I predictably get "Error: 'TO_TIMESTAMP' is not recognized as a function or procedure."

So how do I insert a TIMESTAMP in Derby, and more specifically, how would I convert the above SQL statement to a SQL statement which is executable in Derby?

Solution

  • As Derby is a Java database you will be accessing it through JDBC, therefor you can use the JDBC escape format to specify a timestamp value:

    INSERT INTO TABLE_NAME (COL1, COL2, COL3)
VALUES ('blah', {ts '2011-03-17 15:52:25'}, 'blah');

    But it will require you to reformat the literal value into ISO format.

    The above statement would work with Oracle as well, if run through a JDBC tool.