Converting Oracle date arithmetic to work in HSQLDB
- by JBristow
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.