DATEADD problem ?

[arfgh]

hi there, i want to do this...

Code: Select all   Expand viewCollapse view

SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE "Date_in" > dateadd('mm',-3,curdate())


"Date_in" is a Date type. But for some reasson the gui is saying 'access is denied'. What am i doing wrong ?
using lastest AOO

thx in advance

[charlie.it]

Ciao, DATEADD is non supported in HSQLDB 1.8.x, you need 2.x.x versions, or play with DATEDIFF (if is possible).

[arfgh]

are you saying that DATEADD isnt implemented on AOO SQL ?
in that case, how can i perform the said operation ?

[charlie.it]

Ciao, try:

Code: Select all   Expand viewCollapse view

SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3

[charlie.it]

are you saying that DATEADD isnt implemented on AOO SQL ?

Yes.

[arfgh]

isnt it actually dateadd so basic, that AOO should has it ?

[charlie.it]

It is supported in the new versions (2.x.x). You can use it in a Spilt Database: https://forum.openoffice.org/en/forum/v ... 83&t=61183

[MTP]

AOO is only the "front-end" that stores queries, forms, and reports. The actual SQL is handled by a completely separate "back-end" program, by default version 1.8 of HyperSQL Database (HSQLDB). This version is zipped inside Base so they appear to be one program even though it's two separate programs working together.

Version 1.8 of HSQLDB is old and outdated and DATEADD is not implemented.

Base (the front-end) can work with many other back-ends, including as charlie linked newer versions of HSQLDB that do support DATEADD.

[arfgh]

also embedded without to have to split things ?
howto do it .

anyways, with the datediff use, how to increment then and date and obtain a result date with adding days for example ?

[Villeroy]

also embedded without to have to split things ?

No, but for single-user access you can dump backend, frontend and driver in one folder and use my FreeHSQLDB to build transferable zip archives with an auto-installer macro.
For simultanious multi-user access you need a split database anyway.

[Villeroy]

The attached zip is my database scratch pad with various forms and queries and the auto-install macro from my FreeHSQL package.
1. Create a directory for your single-user access database in a trusted path according to Tools>Options>Security>Macro Security>"Trusted Sources"
2. Download the driver http://hsqldb.org/download/hsqldb.jar into the database directory.
3. Extract the attached zip archive to the database directory. I could have included the hsqldb.jar driver in my package but then the download would be too large for this forum.
4. Open the odb file. The contained auto-open macro will adjust the connection paths to your system and show a success message. The status bar of your copy will read like
jdbc:hsqldb:file:///C:\Path\To\Your\Database\FilterData;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

The file hsqldb.jar in your database directory is the database driver that is used for this particular database.

I added a DATEADD query to the database. The forms and reports are related to various other forum topics.

https://sourceforge.net/projects/hsqldb ... sqldb_2_3/ is the full hsql package with additional tools, documentation and source code. For the interaction with your office suite you only need the driver file hsqldb.jar

[arfgh]

but using all that you are explaining, i have to reject the use of AOO Base ?

[charlie.it]

Ciao, try:

Code: Select all   Expand viewCollapse view

SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3

Does not work? I think so, so it might be enough…

[Villeroy]

but using all that you are explaining, i have to reject the use of AOO Base ?

Is there any reason why you do not check out my database package?

[arfgh]

well, while i try to understand that way to handle db, the main question is still on.

How to add a month to a date, and obtain the result date, with the hsql ebedded in the main AOO ?
datediff isnt for that, so....

thx in advance

[Villeroy]

You can't do it with HSQL 1.8.

[arfgh]

i have for example '2016-06-12', and i want to add a month to it and obtain '2016-07-12'

[Villeroy]

You can't do it with HSQL 1.8.

[MTP]

Well, you can do it, but it's awfully complicated and very easy to mess up the syntax. You have to pull out the year, month, and day as integers from the date, test for which month it is and do a CASEWHEN statement to determine if the day will stay the same (day is 28 or less) or be truncated (day is greater that the amount of days in the following month). This series of conditionals must include testing for a leap year if the day is 29 and the month is January. If the month is December the year must be incremented and instead of adding 1 you subtract 11 from the month number. Then you can take the new year, month, and day integers and CAST them back into a date.

[chrisb]

hello arfgh,

i made this query some years ago. it was used to add 3 months to a date field when using hsqldb 1.8.10.
i have just converted it to add 1 month (hope there are no errors).
as you can see it involves a fair piece of work & you would do well to move on to a split database using hsqldb 2x.

you need to replace all occurrences of "Date" with the name of your date field.
you need to replace the single occurrence of "tMyTable" with your table name.

adding 1 month may sound simple but its not. we cannot have the 31st feb (max 28 or 29 days) or 1st jan (must add one year) or 31st april etc.
copy the code. Database>Queries>Create Query in SQL View. paste the code. hit the icon 'Run SQL command directly'(to show a formatted date field & not the internal number) then execute.

Code: Select all   Expand viewCollapse view

--adds one month to a date field
--produces a result equivalent to the Calc formula '=DATE(YEAR(A2);MONTH(A2)+1;DAY(A2))'
select

"Date",

cast(
case
   when
      month("Date")=12      --going up 1 year. month will be january & have 31 days
   then
      year("Date")+1 || '-01-' || right('0' || day("Date"), 2)
   when
      month("Date")=1 and mod(year("Date"), 4) <> 0   --going into febuary no leap year (max 28 days)
   then
      year("Date") || '-0' ||
      casewhen(day("Date") <= 28,
      '2-' || right('0' || day("Date"), 2),
      '3-0' || day("Date")-28)   
   when
      month("Date")=1 and mod(year("Date"), 4) = 0      --going into febuary and a leap year (max 29 days)
   then   
      year("Date") || '-0' ||
      casewhen(day("Date") <= 29,
      '2-' || right('0' || day("Date"), 2),
      '3-0' || day("Date")-29)   
   when
      month("Date")=3 or month("Date")=5 or month("Date")=8 or month("Date")=10      --next month has max 30 days
   then
      year("Date") || '-' ||
      casewhen(day("Date") <= 30,
      right('0' || month("Date")+1, 2) || '-' || right('0' || day("Date"), 2),
      right('0' || month("Date")+2, 2) || '-01')
   else                                                   --month = 2, 4, 6, 7, 9 or 11.   next month has 31 days
      year("Date") || '-' || right('0' || month("Date")+1, 2) || '-' || right('0' || day("Date"), 2)
end
as DATE)
as "Date+1Month"

from
"tMyTable"

[Villeroy]

Simply turn your database into a safer, more reliable, feature rich and flexible HSQL2 connection. It is a matter of 2 minutes.
Download a hsqldb.jar
Download my extension and install it
put a copy of your file and the driver in one directory.
Extract the database out of your odb and rename the database files script --> foo.script, data --> foo.data etc. where "foo" is an arbitrary database name
Call macro FreeHSQLDB.FreeHSQLDB.Main, connect your script file and the hsqldb.jar

[Krishna1944]

hello

I want to update date of resignation

so i have tried this query :-UPDATE FACULTY SET DOR=dateadd(“YYYY”,60,”dob”);

Error :-1: Access is denied: DATEADD in statement [UPDATE POSITION1 SET doj=dateadd(]

Plz give me solution.

[Villeroy]

1) Create a database directory and put a copy of your Base document in this directory.
2) Create a subdirectory "driver".
3) Download the latest database engine from http://www.hsqldb.org/. This is a zip archive. Extract lib/hsqldb.jar from the archive into the driver directory.
4) Download the attached Writer document from [Python] Macro to extract and reconnect embedded HSQLDB and put it in a "trusted directory" according to menu:Tools>Options>Security>Macro Security
5) Hit the button in that document to install the Python macro.
6) Open your database document in the database folder (1) with the additional driver (3) in subdirectory (2)
7) Call menu:Tools>Macros>Run... pyDBA>ExtractHSQL>Main
8) Test all your query, forms and reports with the new database connection and save the database document.

This database is a lot more safe because it is no longer embedded in a Base document while offering more features, DATEADD for instance.