[Solved] Date arithmetic in SQL in Base

Creating tables and queries

[Solved] Date arithmetic in SQL in Base

Postby RhinoCan » Sat Jan 23, 2016 7:16 am

I'm trying to figure out how to write a WHERE clause in Base's dialect of SQL. I assume I'm using HSQLDB - I'd appreciate someone telling me how to be sure - and have a runtime parameter that I want to use in an expression using date arithmetic.

My WHERE clause is trying to select rows of a table that have an Order_Date within a given range. That range will always be from a Sunday to the following Saturday. I want to supply the date of the relevant Sunday via a runtime variable, then compute the Saturday on the basis that it is 6 days after the Sunday. Therefore, I want something like this:

Code: Select all   Expand viewCollapse view
Where Order_Date between :Week_begin_date and :Week_begin_date + 6 days


The problem is that I can't figure out how to do this in HSQLDB. I can't find any real date calculations in HSQLDB (http://www.hsqldb.org/doc/1.8/guide/ch09.html) beyond one function - DATEDIFF() - that determines the difference between two dates. But I don't see any way to add an interval to a date.

Can anyone tell me if this is possible and, if it is, how to do it?

I've tried the two expressions that would work in DB2 but neither of them work in Base.
Last edited by RhinoCan on Sun Jan 24, 2016 10:16 pm, edited 1 time in total.
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: Date arithmetic in SQL in Base

Postby Villeroy » Sat Jan 23, 2016 1:17 pm

HSQL1.8 knows CURRENT_DATE which returns today's date value and DATEDIFF which returns an integer from 2 dates. That's it.
HSQL2 offers DATEADD which returns a date from another date and an integer: http://hsqldb.org/doc/guide/builtinfunc ... _functions
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date arithmetic in SQL in Base

Postby F3K Total » Sat Jan 23, 2016 1:58 pm

Hello,
find attached an example, containing a workaround for HSQL 1.8.10, not having DATEADD, using
Code: Select all   Expand viewCollapse view
DAYOFWEEK("DATE") = 1
to find all Sundays in a daterange and fill a listbox with these, then
Code: Select all   Expand viewCollapse view
WEEK("DATE")
to find matching rows in form Filter_Weeks. Needs to be expanded with Year("DATE") if the period contains dates from different years.
Find also a second Form Filter_Range, to filter a daterange from a startdate to an enddate.
R
Attachments
Weeks.odb
(64.8 KiB) Downloaded 117 times
Last edited by F3K Total on Sat Jan 23, 2016 5:59 pm, edited 1 time in total.
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Date arithmetic in SQL in Base

Postby charlie.it » Sat Jan 23, 2016 5:32 pm

Congratulations @F3K Total on an excellent idea :super: . I note that the list missing 12 Sundays. How did the list of days?
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

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

Re: Date arithmetic in SQL in Base

Postby F3K Total » Sat Jan 23, 2016 5:39 pm

charlie.it wrote:How did the list of days?
It was randomised with spawner, possible that sundays are missing.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Date arithmetic in SQL in Base

Postby charlie.it » Sat Jan 23, 2016 5:44 pm

I understand, thanks.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

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

Re: Date arithmetic in SQL in Base

Postby F3K Total » Sat Jan 23, 2016 6:24 pm

Hello,
regarding the missing sundays, i modified the database, added a calendar, from 1 year before current year to two years in future. Can be increased in view vCalendar.
Now, no sunday is missing anymore and Year("DATE") will be recognised also.
R
Attachments
Weeks.odb
(65.7 KiB) Downloaded 126 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Date arithmetic in SQL in Base

Postby RhinoCan » Sat Jan 23, 2016 9:00 pm

Villeroy wrote:HSQL1.8 knows CURRENT_DATE which returns today's date value and DATEDIFF which returns an integer from 2 dates. That's it.
HSQL2 offers DATEADD which returns a date from another date and an integer: http://hsqldb.org/doc/guide/builtinfunc ... _functions


I read the syntax of DATEADD and looked at the example so I thought this would work:

Code: Select all   Expand viewCollapse view
WHERE "Order_Date" BETWEEN :Week_Begin_Date AND DATE_ADD(:Week_Begin_Date, INTERVAL 6 DAY)


but not even this would work:

Code: Select all   Expand viewCollapse view
WHERE "Order_Date" BETWEEN :Week_Begin_Date AND DATE_ADD(DATE '2015-12-13', INTERVAL 6 DAY)


All I get is "syntax error" so that's pretty much useless for determining what went wrong. I thought initially it was the variable but if even a hard-coded date doesn't work with the syntax identical to what is in the manual, the only guess I have is that I'm not running HSQL2. How could I determine that if I wanted to be sure?

Given my familiarity with DB2, is there any chance I can use DB2 as the back-end so that I can take advantage of all the power it has? If so, how would I point my queries and reports at DB2?
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: Date arithmetic in SQL in Base

Postby RhinoCan » Sat Jan 23, 2016 9:04 pm

F3K Total wrote:Hello,
regarding the missing sundays, i modified the database, added a calendar, from 1 year before current year to two years in future. Can be increased in view vCalendar.
Now, no sunday is missing anymore and Year("DATE") will be recognised also.
R



Very slick! I like it a lot. I haven't studied it in depth yet to see exactly how it works but I love the idea of having a date chooser or a list of weeks. (Hmm, I'm thinking I might modify this a bit to show me a calendar where an entire week (from Sunday to the following Saturday) is highlighted and the user simply chooses the week he wants from that.)

Thank you very much! Did you have this lying around or did you knock it together in answer to my question?
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: Date arithmetic in SQL in Base

Postby F3K Total » Sat Jan 23, 2016 9:09 pm

Just did it today.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Date arithmetic in SQL in Base

Postby F3K Total » Sat Jan 23, 2016 11:04 pm

Hello RhinoCan
RhinoCan wrote:Hmm, I'm thinking I might modify this a bit to show me a calendar where an entire week (from Sunday to the following Saturday) is highlighted and the user simply chooses the week he wants from that.
I'm really curious about how you solve this bit. Please let us know.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Date arithmetic in SQL in Base

Postby RhinoCan » Sun Jan 24, 2016 9:43 pm

F3K Total wrote:Just did it today.
R


Well, thanks very much for your efforts! I would have worked toward something very similar but it would have taken quite a bit longer since I'm not very fluent with OOO yet. :-)
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: Date arithmetic in SQL in Base

Postby RhinoCan » Sun Jan 24, 2016 9:50 pm

F3K Total wrote:Hello RhinoCan
RhinoCan wrote:Hmm, I'm thinking I might modify this a bit to show me a calendar where an entire week (from Sunday to the following Saturday) is highlighted and the user simply chooses the week he wants from that.
I'm really curious about how you solve this bit. Please let us know.
R


I may have gotten a bit over-ambitious with that remark! I had only glanced at your code at that point and made an assumption that you had written the code for the date-chooser and that I would be able to see and modify that code. Now that I've looked at what you've done, I see that the date-chooser is a built-in control and I see no obvious way to modify it. Otherwise, I might have been able to figure out how to get it to highlight an entire week at a time and then choose the boundary dates from that single selection.

I don't think I want to spend the time to build my own custom controls just so that I can choose a week with a single click; it's not important enough. I can certainly live with choosing a From and To date in two separate manual operations. That's also a lot more flexible approach in case I ever have a situation where I want to do a sales report where the From and To dates are NOT a single week or are for a week that doesn't run from Sunday to Saturday.

I *might* dabble with a custom-control at some point but if I do, it will be sometime down the road. If I get such a control working, I'll try to remember to post it at an appropriate place in the forum.
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: [SOLVED] Date arithmetic in SQL in Base

Postby RhinoCan » Sun Jan 24, 2016 10:17 pm

I'm marking this thread SOLVED. I didn't get all my questions answered but I have a way forward to choose the boundary dates for my report from a form and that's the critical part.
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest