HSQLDB inserting datetime format for SQL Server throws exception

Question

I am trying to run unit tests for my SQL Server query. Query is simply inserting date to the table. I tried two different formats but didn't work:

parameters.addValue(STUDY_DATE, getDate(studyEvent.getStudy().getStudyDate()));

Timestamp timestamp = new Timestamp(getDate(studyEvent.getStudy().getStudyDate()).getTimeInMillis());
parameters.addValue(STUDY_DATE, timestamp);

And this is getDate() method that returns Calendar object:

private Calendar getDate(long time) {
    Calendar calendar = new GregorianCalendar(TimeZone.getTimeZone("America/New_York"));
    calendar.setTimeInMillis(time);
    return calendar;
}

I am not sure if the problem is that SQL Server's datetime format issue or hsqldb issue. Here is my hsqldb create table:

SET DATABASE SQL SYNTAX MSS TRUE;

DROP TABLE event_study IF EXISTS;
CREATE TABLE event_study
(
    STUDY_ID INT,
    STUDY_DATE DATE
)

Is my hsqldb setup wrong? or should I use different datetime format for SQL Server?

Error I am getting is:

data exception: invalid datetime format; nested exception is java.sql.SQLDataException: data exception: invalid datetime format

and SQL query that I am running is:

INSERT INTO event_study(study_id, study_date)
            SELECT x.*
            FROM (VALUES(:study_id, :study_date))
            AS x(study_id, study_date)
            WHERE NOT EXISTS (SELECT 1 FROM event_study s WHERE s.study_id = x.study_id)

Answer 1

As you are not using strings for dates, this is not actually a formatting issue, but a Java type issue. With your table definition, DATE does not have time information. You can create and use a java.sql.Date object for the parameter value. If you want a datetime column, which includes information on time of the day, then use TIMESTAMP in your table definition and a java.sql.Timestamp for the parameter value.

In either case, you cannot use a Calendar object as parameter value.