[Hsqldb-user] Date subtraction, defaults and datatype mismatches in case statements

[Daniel S. <dan...@oc...>]

Hi All,

I've been using HSQLDB as a test environment with an exclusively Hibernate
application for a long time and it works fantastically.

I'm now working on an application which uses raw SQL calls only (for
clarity, robustness and reliability reasons this won't be changing, nor am
I being allowed by my managers to change any of the SQL itself), as such
i've encountered a couple of peculiarities around dates and have no idea
how to get around them.

We use Oracle as our normal database, hence I have set the ora_syntax
variable = true (which I have confirmed, select * from dual works fine).

I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of
peculiarities.

Peculiarity number 1:
select sysdate + 5 from dual - returns what I as an Oracle user expect - 5
days + today

select sysdate - trunc(sysdate) from dual - returns the difference in
seconds. This differs from Oracle, where it would return the result as days.

Is there a default that is used to translate the date subtraction into
using the DATEDIFF function which is choosing seconds, where perhaps in
ora_syntax mode it should be using DAYS?

Even if its not a setting, could anyone direct me to perhaps which class in
the hsqldb library I could look at to modify to change the default from
SECONDS to DAYS, as I don't mind having a bit of a modified version of the
hsqldb library sitting in our artifactory.



Peculiarity number 2:
select sysdate - trunc(sysdate) from dual returns a number (Integer -
number of seconds - ignoring peculiarity number 1 - this is just a number)

select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual

This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible
data types in combination".

Now I have read that in reality HSQLDB is returning a 'datetime interval'
object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot
support it that it is translated into a numeric value at the end - but this
is making it really difficult to test this style of function.


Since the HSQLDB is being used for tests, and the application will run on
Oracle when it is live, is there any way to have a syntax of SQL supported
by both to complete this style of case statement?


Thanks kindly,
Daniel Stoner

This message has been checked for all known viruses by the Postini Virus Control Centre.