How to perform operations on dates in queries?

Question

I'd like to add 100 days to a field in a query:

SELECT DATE_ADD("date" + INTERVAL '100' DAY) FROM "history";

or whatever… but it doesn't work with the default HSQLDB frontend…

Any workaround ?

I am using LibreOffice V 4.1.2.3, maybe I can't do this because of a bug. The field type of "date" is DATE. Does it have to be TIMESTAMP or DATETIME ? — Bogey Jammer, Nov 21 '13 at 12:36
Well, it is apparently not possible with HSQLDB versions below 2.x — Bogey Jammer, Nov 22 '13 at 10:21

score 1 · Answer 1 · answered Nov 20 '13 at 13:40

1

You don't need the date_add(), just add the interval to the column:

SELECT "date" + INTERVAL '100' DAY
FROM "history";

answered Nov 20 '13 at 13:40

@BogeyJammer: apparently you are using an outdated version of HSQLDB then – Nov 21 '13 at 12:59
Right… I've just checked the version embedded in LibO 4.1: v1.8. Sad… – Bogey Jammer Nov 22 '13 at 10:19

fredt · Accepted Answer · 2013-11-22T20:39:30.330

0

The default HSQLDB version in OpenOffic and LO is 1.8. It does not supports this function.

HSQLDB version 2.x supports several functions and expressions for date / time arithmetic.

If the column is named "date", this form is also supported. Note there is no underscore in the function name:

SELECT DATEADD('day',100, "date") FROM "history";

edited Nov 22 '13 at 20:39

answered Nov 20 '13 at 17:10

fredt

2 Answers2