[Solved] Date arithmetic in SQL in Base

[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

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.

[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

[F3K Total]

Hello,
find attached an example, containing a workaround for HSQL 1.8.10, not having DATEADD, using

Code: Select all

DAYOFWEEK("DATE") = 1

to find all Sundays in a daterange and fill a listbox with these, then

Code: Select all

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

[charlie.it]

Congratulations @F3K Total on an excellent idea . I note that the list missing 12 Sundays. How did the list of days?

[F3K Total]

How did the list of days?

It was randomised with spawner, possible that sundays are missing.
R

[charlie.it]

I understand, thanks.

[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

[RhinoCan]

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

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

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?

[RhinoCan]

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?

[F3K Total]

Just did it today.
R

[F3K Total]

Hello RhinoCan

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

[RhinoCan]

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.

[RhinoCan]

Hello RhinoCan

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.

[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.