CODEWITHSUNDEEP
hibernateoracle10ghsqldbdbunit
ArbiPorter
ArbiPorter

Reputation: 65

HSQLDB - Date Format : Incompatible data type in conversion or invalid datetime format

I'm doing integration testing in a Hsqldb. My production database is an Oracle Database.

Versions

Hibernate : 4.1.3.final.

Hsqldb : 2.3.3. ( I can't use the 2.3.4 because it can't run all my junit tests in one click.)

My problems

I met two different exceptions when I had to test methods that contains queries where the WHERE condition asks a date :

org.hsqldb.HsqlException: incompatible data types in combination

or 

org.hsqldb.HsqlException: data exception: invalid datetime format

Codes that caused the exception

The code below caused the first exception (incompatible data types in combination)

 select = "from Player player where player.creationDate = to_char(sysdate,'dd/MM/yy')"

The code below caused the second exception (invalid datetime format)

 select = "from Player player where player.creationDate > '01/01/2016'"

How I tried to fix this

1) So obviously, the problem stems from the date format. Hsqldb supports the 'yyyy-MM-dd' date format so when I changed to_char(sysdate,'dd/MM/yy') and 01/01/2016 into 1995-01-01, it worked fine.

However, I can't change the date format of these methods.

2) On second thoughts, I considered changing the hsqldb source code, but it seemed a little bit radical.

Are there any options than the latter?

Thank you for your time.

Upvotes: 1

Views: 14964

Answers (1)

Apostolos
Apostolos

Reputation: 10498

Dont try to format the date. Use query with named parameters instead. Then you can use setters for all data types. See example below:

String hql = "from Player player where player.creationDate = :date";
List result = session.createQuery(hql)
.setDate("date", new Date()).list();

Upvotes: 2

Related Questions