Page 1 of 1

DATEADD problem ?

Posted: Thu Apr 14, 2016 3:05 pm
by arfgh
hi there, i want to do this...

Code: Select all

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

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:12 pm
by 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).

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:14 pm
by arfgh
are you saying that DATEADD isnt implemented on AOO SQL ?
in that case, how can i perform the said operation ?

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:16 pm
by charlie.it
Ciao, try:

Code: Select all

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

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:18 pm
by charlie.it
arfgh wrote:are you saying that DATEADD isnt implemented on AOO SQL ?
Yes.

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:26 pm
by arfgh
isnt it actually dateadd so basic, that AOO should has it ?

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:35 pm
by charlie.it
It is supported in the new versions (2.x.x). You can use it in a Spilt Database: viewtopic.php?f=83&t=61183

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 3:43 pm
by 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.

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 5:19 pm
by 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 ?

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 5:52 pm
by Villeroy
arfgh wrote: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.

Re: DATEADD problem ?

Posted: Thu Apr 14, 2016 11:26 pm
by 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

Re: DATEADD problem ?

Posted: Fri Apr 15, 2016 1:53 pm
by arfgh
but using all that you are explaining, i have to reject the use of AOO Base ?

Re: DATEADD problem ?

Posted: Fri Apr 15, 2016 2:11 pm
by charlie.it
charlie.it wrote:Ciao, try:

Code: Select all

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… :D

Re: DATEADD problem ?

Posted: Fri Apr 15, 2016 3:25 pm
by Villeroy
arfgh wrote: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?

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 2:35 pm
by 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

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 2:45 pm
by Villeroy
You can't do it with HSQL 1.8.

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 4:05 pm
by arfgh
i have for example '2016-06-12', and i want to add a month to it and obtain '2016-07-12'

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 6:27 pm
by Villeroy
You can't do it with HSQL 1.8.

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 7:29 pm
by 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.

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 8:15 pm
by 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

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

Re: DATEADD problem ?

Posted: Wed Apr 20, 2016 8:49 pm
by 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

Re: DATEADD problem

Posted: Mon Feb 18, 2019 5:47 am
by 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.

Re: DATEADD problem ?

Posted: Mon Feb 18, 2019 11:40 pm
by 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.