Converting Oracle date arithmetic to work in HSQLDB

Question

I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag.

The problem is with the following EJB-QL (simplified a bit):

SELECT o 
FROM Offer o 
WHERE :nowTime 
  BETWEEN o.startDate AND o.startDate + 7

This seems to only work in Oracle's version of SQL.

What's the easiest way for me to convert this to work in both hsqldb and oracle? Assume that changing the two between arguments to named parameters is a very difficult refactor, so I'm going to favor answers that provides a more standardized analog to

o.startdate + 7

---

EDIT: After doing some more research, it looks like Oracle converts the above snippet to

o.startdate + INTERVAL '7' DAY

which is apparently more standard, but doesn't work in HSQLDB.

Answer 1

The HSQLDB syntax for date manipulation is quite different from Oracle. Your best chance is to write a stored procedure in the Oracle DB which simulates the behavior of the HSQLDB functions/procedures.

Answer 2

Your methodology seems correct. I don't know what you're having trouble with. This is working in Oracle and HSQLDB using the script below:

CREATE TABLE OFFER (ID INTEGER, STARTDATE DATE);
INSERT INTO OFFER (ID, STARTDATE) VALUES (1, DATE '2009-01-01');
SELECT ID, STARTDATE, STARTDATE + INTERVAL '7' DAY FROM OFFER;

In both environments I get the results showing the first and the eighth of January.