I have a table where there are numerous date fields, as well as many other types of fields. I then have a client facing form which offers the user many options to filter the query.
One such option is to choose a month and year, so "May" or month "5" and the year "1998" could be chosen.
How can I use the 'Conditions' to filter out all results that are dated within "May 1998"?
Something like
MONTH(AccDate) = $_POST.month AND YEAR(AccDate) = $_POST.year
ie
MONTH(AccDate) = 5 AND YEAR(AccDate) = 1998
but you cannot format the database fields in the Condition.
I was hoping to stick with a normal 'Database Query' rather than using a 'Database Custom Query'.
Create a test Server Action with a Set Value step current_date = {{ NOW }}
See how the date is formatted, and then create new dates as the format (lower and upper bounds) using Set Value. To ensure the correct variable type is set you might need to use the date add formatter with a zero value (e.g. add 0 days)
Essentially you're trying to do a WHERE:
01-05-2020 <= DATE <= 31-05-2020
DATE is within lower and upper bounds.
In your query you put two conditions:
Where date less or equal than upper bound
Where date more or equal than lower bound
Sorry if you don't understand, I wrote like a computer scientist or mathematician
Thanks @Apple, interesting and yes I understand (geekspeak!).
The dates are formatted as
2024-08-05T17:43:14
There is a slight issue with having to find and format the number of days per month, which varies of course.
I could format like this
lower bound : Greater than or equal to
{{$_POST.id_year}}-{{$_POST.id_month}}-01T00:00:00
upper bound : Less than
{{$_POST.id_month==12?$_POST.id_year.toNumber()+1:$_POST.id_year}}-{{$_POST.id_month==12?'01':$_POST.id_month}}-01T00:00:00
The upper bound sets to the 1st of the following month. That way you do a LESS THAN, and avoids having to do a complex date count thing to find the 28th, 29th, 30th & 31st dates. It also rolls over a year if it is December.
