Need a DATE_ADD function or alternative for calculating dates in BASE

[LibreLyrae]

I need a DATE_ADD function or alternative for calculating dates in Base. I have tried DATE_ADD(StartDate,INTERVAL). I have tried the syntax many different ways, normally I get SQL errors saying its expecting a BETWEEN or IN.

If I try DATE_ADD(StartDate, ‘y’) instead of errors I get "The data content could not be loaded… Access is denied DATE_ADD in statement…)

All I want to do is generate a query that will tell me when things are coming due again so I can do a report monthly, weekly, yearly, etc where it will calculate what is due based on its start date or last complete date.

Is there any way to do this in base using SQL, or even in the report builder? Any suggestions would be greatly appreciated. Thanks!

[mariosv]

Find here the documentation page of the HSQLDB what is the embedded database in LibreOffice.
http://hsqldb.org/web/hsqlDocsFrame.html

Calc functions can no be used in BASE, only the SQL functions, look up in http://hsqldb.org/doc/2.0/guide/guide.pdf

[joerose]

I had the same need and found the closed answer to be unhelpful. In my case, I was in need of a query to generate new members within the past 30 days (so, a date_sub(curdate(), interval 30 day) type function would have been perfect). Unfortunately, it looks like Libreoffice does not support date_sub, date_add, etc. However, for my situation, I was able to flip it around and use a function that is supported, datediff().

This works: AND DATEDIFF( ‘day’, “Members”.“DateJoined”, CURDATE( ) ) <= 30

[iplaw67]

http://www.hsqldb.org/doc/1.8/guide/guide.html#N1251E

would seem to indicate that hsqldb 1.8, which comes with LO and is the default engine for embedded databases, does not have a DATE_ADD function. You would need either to use a Basic macro to code this and execute a corresponding prepared statement, or else change your database engine to one that does have that function.

[黄智壯]

To expand on @mariosv’s answer, HSQLDB date functions can be found at:

http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_datetime_functions