Forums

I am attempting to create a query which reads from the database and counts the number of entries based on a selected date, the field that I am retrieving the date from is a date-time field in the format Y-m-d h:i:s.

(At the moment I am using a static date and month, however, I will move it to a lookup of dropdowns)

What is the correct syntax so that the hours, mins & seconds are ignored in the query?

Regards,

Dave

When I use

Job1.created:2017-%-%

Or

Job1.created:2017

The returned array is:

[read_year_data] => Array
        (
            [log] => Array
                (
                    [0] => SELECT COUNT(`Job1`.`id_job`) AS `Job1.count` FROM `#_aa_job_data` AS `Job1` WHERE `Job1`.`created` = '2017-%-%' LIMIT 10000;
                )

            [var] => 0
        )

When I execute the equivalent in phpMyAdmin it returns the correct number of rows...

SELECT COUNT(`id_job`) FROM #_aa_job_data WHERE YEAR(created) = '2017' ;