I need some help! Trying to recreate the following query in Wappler that works fine in SQL. I have been trying to get this to work for three days so any assistance greatly appreciated.
SELECT * FROM visits WHERE dateArr > DATE_FORMAT(NOW() ,’%Y-08-01’)
Have you tried custom query?
Yes, I have. Bizarrely it returns all dates rather than those set out in the query.
What is the typical content of the field dateArr and what is it’s field type?
Thanks for your reply. It is a date field.
As i thought, nothing wrong with the query
Just set up, a simple table
wrote a custom query in navicat
select * from visits where dateArr > DATE_FORMAT(NOW() ,'%Y-08-01' )
this returns the correct result (2020-07-30 ignored)
So we know the query is correct, will try in Wappler when i get 5 mins in case it’s a wappler issue
Thanks Hyperbytes it worked fine in php admin for me too, but gobbledygook in wappler
Ok, spotted the error, I should have see this straight away
There is no need for the “%” in the date format statement so it actually resolves to “%2020-8-1”
Take, the % sign out it will work fine
@George, any reason for the difference in interpretation of the %, needed in MySQL but breaks the query if included in Wappler?
@patrick probably has a better answer
Can you tell me why the % is not needed, checked the documentation for DATE_FORMAT and it would require the %Y.
I tested with a custom query on PHP 7.3 and a MySQL 8 database and it worked fine. What is the query that it returns when you enable the Debug?
Here is the issue @patrick
The data
Custom query (works correctly)
Query Builder (using percent sign)
Output:
Incorrectly listing the entry for 2020-07-30
Query builder without percent
Output (correct)
Video of process
query_issue
You are doing 2 different things, formatDate and DATE_FORMAT are not the same functions.
DATE_FORMAT is a function from MySQL and is run on the database, the NOW() returns the datetime in the timezone set in MySQL.
formatDate is a formatter and runs in PHP, the NOW variable is the server time in the timezone set in PHP.
But NOW and NOW() can return different times depending on the configuration. The correct format for the formatDate formatter is actually NOW.formatDate('yyyy-08-01'), but on PHP Y would also work.
Brilliant, thanks everyone for your help
