Ask Your Question
0

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
1

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. 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

edit flag offensive delete link more

Comments

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
1

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

Alex Thurgood gravatar image

http://www.hsqldb.org/doc/1.8/guide/g...

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
0

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

黄智壯 gravatar image

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

edit flag offensive delete link more

Question Tools

Stats

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

Seen: 2,200 times

Last updated: Apr 06 '14