DATEADD problem ?

Creating tables and queries

DATEADD problem ?

Postby arfgh » Thu Apr 14, 2016 3:05 pm

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
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby charlie.it » Thu Apr 14, 2016 3:12 pm

Ciao, DATEADD is non supported in HSQLDB 1.8.x, you need 2.x.x versions, or play with DATEDIFF (if is possible).
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

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

Re: DATEADD problem ?

Postby arfgh » Thu Apr 14, 2016 3:14 pm

are you saying that DATEADD isnt implemented on AOO SQL ?
in that case, how can i perform the said operation ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby charlie.it » Thu Apr 14, 2016 3:16 pm

Ciao, try:

Code: Select all   Expand viewCollapse view
SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

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

Re: DATEADD problem ?

Postby charlie.it » Thu Apr 14, 2016 3:18 pm

arfgh wrote:are you saying that DATEADD isnt implemented on AOO SQL ?

Yes.
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

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

Re: DATEADD problem ?

Postby arfgh » Thu Apr 14, 2016 3:26 pm

isnt it actually dateadd so basic, that AOO should has it ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby charlie.it » Thu Apr 14, 2016 3:35 pm

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
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

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

Re: DATEADD problem ?

Postby MTP » Thu Apr 14, 2016 3:43 pm

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
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: DATEADD problem ?

Postby arfgh » Thu Apr 14, 2016 5:19 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby Villeroy » Thu Apr 14, 2016 5:52 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Postby Villeroy » Thu Apr 14, 2016 11:26 pm

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

Re: DATEADD problem ?

Postby arfgh » Fri Apr 15, 2016 1:53 pm

but using all that you are explaining, i have to reject the use of AOO Base ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby charlie.it » Fri Apr 15, 2016 2:11 pm

charlie.it wrote: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… :D
charlie
macOS 10.12 Sierra: Open Office 4.1.5 - LibreOffice 6.2

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

Re: DATEADD problem ?

Postby Villeroy » Fri Apr 15, 2016 3:25 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Postby arfgh » Wed Apr 20, 2016 2:35 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby Villeroy » Wed Apr 20, 2016 2:45 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Postby arfgh » Wed Apr 20, 2016 4:05 pm

i have for example '2016-06-12', and i want to add a month to it and obtain '2016-07-12'
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: DATEADD problem ?

Postby Villeroy » Wed Apr 20, 2016 6:27 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem ?

Postby MTP » Wed Apr 20, 2016 7:29 pm

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
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: DATEADD problem ?

Postby chrisb » Wed Apr 20, 2016 8:15 pm

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"
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 191
Joined: Mon Jun 07, 2010 4:16 pm

Re: DATEADD problem ?

Postby Villeroy » Wed Apr 20, 2016 8:49 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DATEADD problem

Postby Krishna1944 » Mon Feb 18, 2019 5:47 am

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
Krishna1944
 
Posts: 2
Joined: Mon Feb 18, 2019 5:39 am

Re: DATEADD problem ?

Postby Villeroy » Mon Feb 18, 2019 11:40 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests