How to add days to date in OO BASE?

[Victor P]

SELECT '2015-10-10' + 3 DAYS as "DATE" From table

How to add some days to date in Open Office Base?

[robleyd]

Depends on what database you are using with Base - the default embedded HSql or something else. Look at the left of the status bar to get this information.

Click image to see a larger version

See for example Increase Dates by 1 year for some ideas.

[Victor P]

Embedded database HSQL database engine

DATEADD('dd',30,"date_column") AS "new_date" FROM "table"
Syntax error in SQL expression

PLEASE help: how to add some days to date ?

[robleyd]

Did you not read the link Increase Dates by 1 year I posted? It mentions that the embedded database does not support DATEADD. In that link are some examples of using CAST to increase a date. See for example Sliderule's post in that topic.

[Villeroy]

The best solution involves:
1) Extraction of the embedded HSQLDB 1.8
2) Download HSQL 2.4 and use it with the extracted database. 2.4 is the last version able to convert 1.8.
3) Optionally, you may want to upgrade further.
HSQL 2.x has a DATEADD function. More importantly, your data are much safer in a stand-alone database. With an embedded database you will lose all your data sooner or later, and then you need a recent backup copy of that database.

WIth embedded HSQLDB 1.8 you can generate a calendar in Calc, just a simple list of all dates in the near past and future, copy the list into the database and use that table as in
SELECT TBL.*, CAL.D AS "+3 days" FROM TBL, CAL WHERE DATEDIFF('dd', TBL."Date", CAL.D) = 3

I recommend viewtopic.php?f=21&t=86071 which performs most of the steps semi-automatically. You just need a directory for the database document(s) and a subdirectory for the drivers. The macro will add another directory with the actual database files extracted from the document(s).

[Victor P]

This was good, I just needed to change column format to Date.

SELECT
CAST(YEAR("D") + 1 ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off"
FROM "Somewhere"

[Villeroy]

This was good, I just needed to change column format to Date.

SELECT
CAST(YEAR("D") + 1 ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off"
FROM "Somewhere"

Your initial question was how to add days. This solution fails with months, days, hours, minutes, seconds.

[Victor P]

This was good, I just needed to change column format to Date.

SELECT
CAST(YEAR("D") + 1 ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off"
FROM "Somewhere"

Your initial question was how to add days. This solution fails with months, days, hours, minutes, seconds.

Yep it works only with Years.

Could you explain where I should download what you mentioned:
1) Extraction of the embedded HSQLDB 1.8
2) Download HSQL 2.4 and use it with the extracted database. 2.4 is the last version able to convert 1.8.

[Villeroy]

HSQL 2.4.1: https://sourceforge.net/projects/hsqldb ... p/download is the last version able to convert HSQL 1.8 and contains the driver file /hsqldb-2.4.1/hsqldb/lib/hsqldb.jar
/hsqldb-2.4.1/hsqldb/doc/guide/builtinfunctions-chapt.html is the documentation on built-in functions including DATEADD.
[Python] Macro to extract and reconnect embedded HSQLDB

[Victor P]

HSQL 2.4.1: https://sourceforge.net/projects/hsqldb ... p/download is the last version able to convert HSQL 1.8 and contains the driver file /hsqldb-2.4.1/hsqldb/lib/hsqldb.jar
/hsqldb-2.4.1/hsqldb/doc/guide/builtinfunctions-chapt.html is the documentation on built-in functions including DATEADD.
[Python] Macro to extract and reconnect embedded HSQLDB

I don't understand how to add all this to OO Base. I'm just learning SQL now Maybe You could make video on Youtube with step by step instruction?

[Villeroy]

It's all in the macro description.
Create a directory and copy your origninal database document into that directory
Add a subdirectory "driver" to that directory.
Download and open the above linked zip archive containing HSQL 2.4.1, open it and extract /hsqldb-2.4.1/hsqldb/lib/hsqldb.jar into the driver directory.
Download and open my text document and click the [Install] button.
Open the copy of your origninal database document and run the macro.

[Villeroy]

https://www.mediafire.com/file/qpiuiq0s ... e.zip/file is a database with DATEADD.
Download the zip and extract the contained folder "due_date_template" to a trusted folder where trusted document macros are allowed to run. It contains a database document, a driver folder with hsqldb.jar and a database folder with the actual HSQLDB. See Tools>Options>Security>[Macro Security...] > Trusted Sources.
Open the database. The macro pops up a message that the document has been connected with the files in the database folder.
----
Now copy your database document into the same folder as my database document and run my Python macro pyDBA->ExtractHSQLDB

[charlie.it]

This code calculates the days of difference between the date "D" and the date "1900/01/01", then adds the days of the "Days" field. The result is the new "New Date" in "serial" format.
To see the result correctly in a table, you need to reformat it in date format or insert it into a form.

Code: Select all

SELECT *, DATEDIFF( 'DD', '1900-01-01', "D" ) + "Days" + 2 AS "New Date" FROM "Somewhere"

[Villeroy]

This code calculates the days of difference between the date "D" and the date "1900/01/01", then adds the days of the "Days" field. The result is the new "New Date" in "serial" format.
To see the result correctly in a table, you need to reformat it in date format or insert it into a form.

Very interesting solution. Certainly good enough to display some calculated date in forms, reports, mail merge and linked spreadsheets.
This works well for small record sets. It becomes slow when you do this with thousands.
Instead of "Date" - 1900-01-01 +2 you can also use "Date" - 1899-12-30 (day zero of this office suite).
For some reason I don't know, SELECT *, ... FROM "Somewhere" works in direct SQL mode but not in parsed mode. In parsed mode you have to specify the table name with the asterisk: SELECT "Somewhere".*, ... FROM "Somewhere"

You have to be aware that the calculated result is an integer formatted by the office suite. The database engine can not interprete integers as dates, for instance MONTH("New Date") would fail because from the perspective of the database engine an integer has no month.

Code: Select all

SELECT "Somewhere".*, DATEDIFF( 'DD', '1899-12-30', "D" ) + "Days" AS "New Date" FROM "Somewhere"

[charlie.it]

1899-12-30 (day zero of this office suite)

I didn't know, I never stop learning .

... works in direct SQL mode but not in parsed mode ...

I confirm, I wrote the code without trying it.

calculated result is an integer formatted by the office suite

I confirm, the result is only for display.
Thank you.