I created a simple view filtered by a specific day( I will switch this out for a variable when I use this on the website). The field is a CCK date field. The view works great. The SQL it generates is as follows: It just prints a list of titles from published nodes on a specific date (cck date field, not node date)
SELECT node.nid AS nid, node.title AS node_title FROM node node LEFT JOIN content_field_event_date_time node_data_field_event_date_time ON node.vid = node_data_field_event_date_time.vid WHERE (node.status = 1) AND(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date_time.field_event_date_time_value), SEC_TO_TIME(-25200)), '%Y-%m-%%d') = '2012-08-21')
I took this query and pasted it into the Query box in Phpmyadmin and it returns 0 rows. If I remove this part:
AND(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date_time.field_event_date_time_ value), SEC_TO_TIME(-25200)), '%Y-%m-%%d') = '2012-08-21')
and it returns all the rows excluding the date filtering (which is what I really need).
Eventually I want to do a count operation and use php to have this printed in a custom view field (or other places) to show the total events on a specific day.
Is there something specific I need to do with the formatting to make this query work in Phpmyadmin like it does in views? Or is something else wrong?