Date manipulation

I want to set a date range in a query to be between TODAY -10 Days and TODAY + 10 days. Is there a simple way to do this?

Regards

Peter Goggin

Hi Peter,

I want to set a date range in a query to be between TODAY -10 Days and
TODAY + 10 days. Is there a simple way to do this?

You need a function like DATEADD (MySQL/MariaDB). This function isn't
included in the HSQLDB.
The first time I tried this I created a table by using Calc. One column
a Integer, second column Date. Let Calc added all Dates up to the time I
nedded. Then I could look into this table for my date (Today), count 10
for the Integer down and look for the new date.
If you don't use the date as DATE in the HSQLDB you could use something
like this:
DATEDIFF('dd','1899-12-30',TODAY)+10
You could format the column to date and the GUI will change this to the
date you want. But remember: The format of the query will never been
saved. So it could only be usable in a form.

Regards

Robert

Hi

Robert Großkopf wrote

you could use something like this:
DATEDIFF('dd','1899-12-30',TODAY)+10

+1

Robert Großkopf wrote

You could format the column to date and the GUI will change this to the
date you want. But remember: The format of the query will never been
saved. So it could only be usable in a form.

I think it will be saved if you create a view from the query, open the view,
then change the format.

Regards
Pierre-Yves

I found that the following code did what I want.:
  AND DAYOFYEAR( "Contacts"."ContactFollowUpDate" ) BETWEEN DAYOFYEAR( "TODAY" ) - 15 AND DAYOFYEAR( "TODAY" ) + 15
This gives me all contacts due within the last 15 days and the next 15 days.

Obviously this will have a problem between 15th Dec to 15th January, but since I will not need to generate the report in this time period the code will work.

Regards

Peter Goggin