Between two dates no longer works

Wappler General How To
1

Have just tried to show some records with my date_added field set to NOW and I get the following error: er_wrong_value: incorrect date value when I execute query.

I had a query which showed records records between two dates, Now and -60 days. This query has stopped working recently having worked for several months. The simple query above is an attempt to figure out what’s going wrong.

Original query was date.date_added between {{NOW.dateAdd(‘days’,-60}} AND {{NOW}}

The above no longer shows any records even when there are records within this day period.

Date field type is date and shows as 2022-12-12

2

It might just be a typo in your post but you’re missing a closing ) after the -60

3

Yes, sorry that’s a typo, the actual query is correct

4

Where do you get this error?

Have you changed query types such as regular query to paged query?

5

Please provide more details.

Where do you see this exactly?
Also please post a screenshot of your query step setup.

6

It looks like a mySQL error text. You may want to cast the date format to what it expects by default using DATE_FORMAT

7

Thank you all for your suggestions, below are some images to try and explain clearly whats happening.

Firstly may I reiterate, my query worked perfectly until very recently, I can’t be specific as to when it stopped working as I don’t watch what the page is doing on a daily basis.

I have now created a test query with only the between date condition to make sure nothing else is effecting the result.

If setup the date.added field to look between two dates, {{NOW.dateAdd(‘days’,-60)}} AND {{NOW}}, when I run the ‘execute query’ within the database query builder I get no resultswhen there are clearly plenty of records that should be there;

date-between-1
date-between-11020×747 59.9 KB

date-between-3
date-between-4
date-between-4338×744 17.5 KB

If I do a very simple query to just show all records for today, data.added EQUALS {{NOW}} I get the following error in the ‘execute query’ within the database query manager;

date-between-2
date-between-21020×747 61.8 KB

Hope this problem is now clear and someone might be able to help.

8

So the problem is that in the query builder when you test your query, using {{NOW}} expression as a value you see the error?
Have you tried adding real date values instead… ?

Is there any issue at all when running your server action in the browser?

9

Hi Teodor,

If I run the very simple query {{NOW}} I get the correct result of the three records with todays date.

I have also just tried the more complex BETWEEN query {{NOW.dateAdd(‘days’,-60)}} AND {{NOW}}, it is now showing results in the test page so my matter is resolved.

Guess its an issue with the ‘execute query’ within Database Query Manager, I’ll wait for an update before using it again.

Thank you all for your help once again…

10

Expressions like these are not supported in the query builder. You can enter specific values and test.

11

More specifically, “Expressions like these are not supported in the query builder”

May we see examples of what we are avoiding in this case, please?

12

Just avoid using NOW or NOW_UTC or a binding to a value etc.

You should be using a fixed value for testing (specifically on the parameters tab when setting the test values)

2 Likes