### [Solved] Date arithmetic in SQL in Base

Posted:

**Sat Jan 23, 2016 7:16 am**I'm trying to figure out how to write a WHERE clause in Base's dialect of SQL. I assume I'm using HSQLDB - I'd appreciate someone telling me how to be sure - and have a runtime parameter that I want to use in an expression using date arithmetic.

My WHERE clause is trying to select rows of a table that have an Order_Date within a given range. That range will always be from a Sunday to the following Saturday. I want to supply the date of the relevant Sunday via a runtime variable, then compute the Saturday on the basis that it is 6 days after the Sunday. Therefore, I want something like this:

The problem is that I can't figure out how to do this in HSQLDB. I can't find any real date calculations in HSQLDB (http://www.hsqldb.org/doc/1.8/guide/ch09.html) beyond one function - DATEDIFF() - that determines the difference between two dates. But I don't see any way to add an interval to a date.

Can anyone tell me if this is possible and, if it is, how to do it?

I've tried the two expressions that would work in DB2 but neither of them work in Base.

My WHERE clause is trying to select rows of a table that have an Order_Date within a given range. That range will always be from a Sunday to the following Saturday. I want to supply the date of the relevant Sunday via a runtime variable, then compute the Saturday on the basis that it is 6 days after the Sunday. Therefore, I want something like this:

- Code: Select all Expand viewCollapse view
`Where Order_Date between :Week_begin_date and :Week_begin_date + 6 days`

The problem is that I can't figure out how to do this in HSQLDB. I can't find any real date calculations in HSQLDB (http://www.hsqldb.org/doc/1.8/guide/ch09.html) beyond one function - DATEDIFF() - that determines the difference between two dates. But I don't see any way to add an interval to a date.

Can anyone tell me if this is possible and, if it is, how to do it?

I've tried the two expressions that would work in DB2 but neither of them work in Base.