Page 1 of 1

[Solved] Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 7:16 am
by RhinoCan
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.

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 1:17 pm
by Villeroy
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

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 1:58 pm
by F3K Total
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

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 5:32 pm
by charlie.it
Congratulations @F3K Total on an excellent idea :super: . I note that the list missing 12 Sundays. How did the list of days?

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 5:39 pm
by F3K Total
charlie.it wrote:How did the list of days?
It was randomised with spawner, possible that sundays are missing.
R

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 5:44 pm
by charlie.it
I understand, thanks.

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 6:24 pm
by F3K Total
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

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 9:00 pm
by RhinoCan
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?

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 9:04 pm
by RhinoCan
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?

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 9:09 pm
by F3K Total
Just did it today.
R

Re: Date arithmetic in SQL in Base

PostPosted: Sat Jan 23, 2016 11:04 pm
by F3K Total
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

Re: Date arithmetic in SQL in Base

PostPosted: Sun Jan 24, 2016 9:43 pm
by RhinoCan
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. :-)

Re: Date arithmetic in SQL in Base

PostPosted: Sun Jan 24, 2016 9:50 pm
by RhinoCan
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.

Re: [SOLVED] Date arithmetic in SQL in Base

PostPosted: Sun Jan 24, 2016 10:17 pm
by RhinoCan
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.