Ask Your Question

Need a DATE_ADD function or alternative for calculating dates in BASE. [closed]

asked 2013-03-02 15:12:15 +0200

Libre Lyrae gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 18:51:10.697664

3 Answers

Sort by » oldest newest most voted

answered 2013-03-02 20:25:05 +0200

m.a.riosv gravatar image

Find here the documentation page of the HSQLDB what is the embedded database in LibreOffice.

Calc functions can no be used in BASE, only the SQL functions, look up in

edit flag offensive delete link more


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

joerose gravatar imagejoerose ( 2018-07-09 21:58:00 +0200 )edit

answered 2014-04-05 15:15:18 +0200

Alex Thurgood gravatar image

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.

edit flag offensive delete link more

answered 2014-04-06 09:50:56 +0200

黄智壯 gravatar image

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

edit flag offensive delete link more

Question Tools


Asked: 2013-03-02 15:12:15 +0200

Seen: 2,520 times

Last updated: Apr 06 '14