How to filter date-range entries by todays date or another date

goedible · June 4, 2023, 5:07am

How can I filter end > todays date > start? I tried this:

“annual_cultivation_dates”.“end_month” >= MONTH( today( ) ) AND “annual_cultivation_dates”.“end_day” >= DAY( today( ) ) AND “annual_cultivation_dates”.“start_month” <= MONTH( today( ) ) AND “annual_cultivation_dates”.“start_day” <= DAY( today( ) )

When I use the GUI I can not save it and I get this error:
Syntax error in SQL statement /home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:109
It does work and run…

How do I improve the formula in the event that “start_month” > “end_month”
for example harvest potatoes “start_month” = 6 and “end_month” = 2

I have a table “day” with only one entry and columns "month’ and “day”. How can I set the default entries to be month(today()) and day(today()) and use these records for filtering “annual_cultivation_dates”?

Wanderer · June 4, 2023, 5:55am

You know you can edit the query in SQL? There you also have a toggle to query the database “directly”, without interference by Base. Needed for all SQL, wich is not understood by Base…

Villeroy · June 4, 2023, 3:33pm

PowerFilter.odb (85.5 KB)

goedible · June 4, 2023, 4:14pm

Yes, I know.

cpb · June 4, 2023, 2:40pm

EDIT: replaced the word TIMESTRING with TIMESTAMP.
EDIT_2 updated code as stated in my second comment

you do not disclose the database used.

embedded HSQLDB will not recognise ‘TODAY()’, it will however recognise ‘NOW()’ which returns a timestamp, you require a DATE not a TIMESTAMP.

because your start values may be either lesser OR greater than your end values it makes sense to include a calculated YEAR value as shown in the WHERE clause.

this code should work OK with the default HSQLDB embedded database.

select
	*
from
	"annual_cultivation_dates_view"
where
	current_date between
		cast(
			year(current_date)
			|| '-' ||
			right('0' || "start_month", 2)
			|| '-' ||
			right('0' || "start_day", 2)
		as date)
		and
		cast(
			case
				when "start_month" <= "end_month" then
					year(current_date)
				else
					year(current_date) + 1
			end
			|| '-' ||
			right('0' || "end_month", 2)
			|| '-' ||
			right('0' || "end_day", 2)
		as date)

goedible · June 4, 2023, 4:28pm

This makes sense to me. I am having problems doing it.

in case it helps, here a link to the database Dropbox - LANdpLAN - Simplify your life

cpb · June 4, 2023, 6:05pm

@ goedible

why should anyone trying to help be forced to download a database?

goedible · June 4, 2023, 6:42pm

What does it mean to “disclose the databse used”?
What do you mean by forced? I think people can comment without needing to download.

Villeroy · June 4, 2023, 7:06pm

select * from "annual_cultivation_dates"
where current_date between 
CAST( ... AS DATE)
and
CAST( ... AS DATE)

and for debugging select the concatenations as well.

cpb · June 4, 2023, 9:21pm

@goedible,

it’s vital to know what database and version of that database you use, HSQLDB, MySQL, PostgreSQL etc.
you use HSQLDB 2.3.3.

I have made 3 alterations to the original code:
table NOW = "annual_cultivation_dates_view"
replaced “start_month” < "end_month" WITH “start_month” <= “end_month” (because a cultivation period may only span 1 month).
added the cast function (required by the particular database you use)

you have a number of errors in the TABLE “annual_cultivation”:
you use 4 fields for day and month, “start_day”, “start_month”, “end_day” and “end_month”.
this method is susceptible to input errors as proven here.
why not use a date field, the value of year is unimportant but should not be a leap year.
here is a list of the errors which are causing the issue:

correct your input errors and the updated query will run as expected.

goedible · August 13, 2023, 11:11pm

cpb:

select
	*
from
	"annual_cultivation_dates_view"
where
	current_date between
		cast(
			year(current_date)
			|| '-' ||
			right('0' || "start_month", 2)
			|| '-' ||
			right('0' || "start_day", 2)
		as date)
		and
		cast(
			case
				when "start_month" <= "end_month" then
					year(current_date)
				else
					year(current_date) + 1
			end
			|| '-' ||
			right('0' || "end_month", 2)
			|| '-' ||
			right('0' || "end_day", 2)
		as date)

This is working now! Thank you.

goedible · December 13, 2023, 6:53pm

It was working until today I got and error. One of the dates entered was wrong 09/31 (I blame the Romans for making the calendar more complicated than necessary!)

"SQL Status: 22007
Error code: -3407

data exception: invalid datetime format /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:175"

goedible · January 29, 2024, 3:11am

This was working until Jan 1st. Now None of the entries with a start date Higher than one are showing. For example start December to end February. I’ll see if I can modify what you have given me.

goedible · January 29, 2024, 3:12am

This modification fails because it returns values where end date is before current date.
select
*
from
“annual_cultivation_dates_view”
where
current_date between
cast(
case
when “start_month” <= “end_month” then
year(current_date)
else
year(current_date) - 1
end
|| ‘-’ ||
right(‘0’ || “start_month”, 2)
|| ‘-’ ||
right(‘0’ || “start_day”, 2)
as date)
and
cast(
case
when “start_month” <= “end_month” then
year(current_date)
else
year(current_date) + 1
end
|| ‘-’ ||
right(‘0’ || “end_month”, 2)
|| ‘-’ ||
right(‘0’ || “end_day”, 2)
as date)

goedible · January 29, 2024, 3:32am

This worked! Thanks again.

select
*
from
“annual_cultivation_dates_view”
where
current_date <
cast(
year(current_date)
|| ‘-’ ||
right(‘0’ || “end_month”, 2)
|| ‘-’ ||
right(‘0’ || “end_day”, 2)
as date)
and
current_date between
cast(
case
when “start_month” <= “end_month” then
year(current_date)
else
year(current_date) - 1
end
|| ‘-’ ||
right(‘0’ || “start_month”, 2)
|| ‘-’ ||
right(‘0’ || “start_day”, 2)
as date)
and
cast(
case
when “start_month” <= “end_month” then
year(current_date)
else
year(current_date) + 1
end
|| ‘-’ ||
right(‘0’ || “end_month”, 2)
|| ‘-’ ||
right(‘0’ || “end_day”, 2)
as date)