Unable to insert Date into HSQLdb

I am using HSQL as an in memory database for testing. However, I am unable to insert a date into the column. Below is my table creation script:

SET DATABASE SQL SYNTAX ORA TRUE;

create table OWN_TABLE (
    ID DECIMAL NOT NULL,
    NAME VARCHAR(24) NOT NULL,
    CAPACITY_SUPPLY_DATE DATE ,
);

And the queries I tried:

INSERT INTO OWN_TABLE(ID, NAME, CAPACITY_SUPPLY_DATE) VALUES(2, '4813', '2090-07-15');

But it gives

Caused by: java.sql.SQLDataException: data exception: invalid datetime format

I also tried 2090-07-15 00:00:00 but it didn't work.

Assuming oracle syntax might work, I tried:

INSERT INTO LABS_CAPACITY_SUPPLY(ID, SHIP_NODE, CAPACITY_SUPPLY_DATE) VALUES(1, '4813', 'TO_DATE(2090-07-30,"yyy-MM-dd")');

But got Unparseable date: "TO_DATE(2090-07-30,"yyy-MM-dd")"

Can somebody please tell me the right way to insert dates into HSQL. I have the 2.3.2 jar.

Solution

Use a proper standard SQL date literal:

INSERT INTO OWN_TABLE
  (ID, NAME, CAPACITY_SUPPLY_DATE) 
VALUES
  (2, '4813', DATE '2090-07-15');

Or, if you do want to use the to_date() function then don't put it into single quotes.

INSERT INTO LABS_CAPACITY_SUPPLY
  (ID, SHIP_NODE, CAPACITY_SUPPLY_DATE) 
VALUES
  (1, '4813', TO_DATE(2090-07-30,'yyy-MM-dd'));

Note that for the to_date() function the case of the format string does not matter. 'yyyy-MM-dd' is the same as 'YYYY-MM-DD' or 'yyyy-mm-dd'. The format is not the one used for Java's SimpleDateFormat.

If you are doing this from within a Java program you should not use any of the above solutions. Use a PreparedStatement instead:

java.sql.Date dt = ...;
PreparedStatement pstmt = connect.prepareStatement("INSERT INTO OWN_TABLE(ID, NAME, CAPACITY_SUPPLY_DATE) VALUES(?,?,?)";
pstmt.setInt(1, 2);
pstmt.setString(2, "4813");
pstmt.setDate(3, dt);

How you construct the instance of java.sql.Date depends on where you get the data from. You could use a SimpleDateFormat to parse the user's input. Or you could use LocalDate if you are using Java 8, e.g. java.sql.Date.valueOf(LocaleDate.of(2090,7,30))