Data migration from MySQL to HSQL

Question

I was working on migrating data from MYSQL to HSQL. In MYSQL data file, there are plenty of records where date values are set as '0000-00-00' and HSQL database throws below error

"data exception: invalid datetime format / Error Code: -3407 / State: 22007"

for all such records.

I would like to know what could be optimum solution for this problem?

Thanks in advance

Answer 1

HSQLDB follows the SQL Standard and allows valid dates only. A date such as '0001-01-01' would be a good candidate for the default value.

Regardless of the method used for data inserts, the '0000-00-00' strings should be corrected before insert. One way of doing this is to use a default value for the target column with DEFAULT DATE'0001-01-01' and replace the string in the INSERT statement with the keyword DEFAULT. For example:

CREATE TABLE MYTABLE ( C1 INT, C2 DATE DEFAULT DATE'0001-01-01')
INSERT INTO MYTABLE VALUES 1, DEFAULT
INSERT INTO MYTABLE VALUES 3, '2010-08-14'