DATEADD problem ?

Creating tables and queries
Post Reply
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

DATEADD problem ?

Post 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
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: DATEADD problem ?

Post 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).
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post by arfgh »

are you saying that DATEADD isnt implemented on AOO SQL ?
in that case, how can i perform the said operation ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: DATEADD problem ?

Post by charlie.it »

Ciao, try:

Code: Select all

SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: DATEADD problem ?

Post by charlie.it »

arfgh wrote:are you saying that DATEADD isnt implemented on AOO SQL ?
Yes.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post by arfgh »

isnt it actually dateadd so basic, that AOO should has it ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: DATEADD problem ?

Post 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
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: DATEADD problem ?

Post 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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post 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 ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post 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
Attachments
FilterData_HSQL2.zip
(118.46 KiB) Downloaded 425 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post by arfgh »

but using all that you are explaining, i have to reject the use of AOO Base ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: DATEADD problem ?

Post 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
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post 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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post 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
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post by Villeroy »

You can't do it with HSQL 1.8.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Post by arfgh »

i have for example '2016-06-12', and i want to add a month to it and obtain '2016-07-12'
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post by Villeroy »

You can't do it with HSQL 1.8.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: DATEADD problem ?

Post 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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: DATEADD problem ?

Post 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"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Krishna1944
Posts: 2
Joined: Mon Feb 18, 2019 5:39 am

Re: DATEADD problem

Post 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.
OpenOffice 4.1.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply