I’m trying to run a reporting query that lets the user choose a date range. If I comment out the date range where clauses, it works fine pulling all records. I’ve tried a lot of different approaches, and nothing seems to work.
The dates come in the format of a GET variable as ‘mm-dd-yyyy’
{exp:query sql=
"SELECT
DATE_FORMAT(FROM_UNIXTIME(exp_channel_titles_order.entry_date), '%m-%d-%Y') AS entry_date,
exp_channel_titles_release.title AS title,
exp_channel_data_release.field_id_5 AS rel_diamond_id,
exp_channel_data_item.field_id_174 AS purchased_id,
exp_channel_data_item.field_id_177 AS purchased_order_id,
exp_channel_data_item.field_id_176 AS purchased_price,
exp_channel_data_order.field_id_171 AS order_payment_gateway
FROM exp_channel_titles exp_channel_titles_item
INNER JOIN exp_channel_data exp_channel_data_item ON exp_channel_titles_item.entry_id=exp_channel_data_item.entry_id
INNER JOIN exp_channel_data exp_channel_data_release ON exp_channel_data_item.field_id_174=exp_channel_data_release.entry_id
INNER JOIN exp_channel_titles exp_channel_titles_release ON exp_channel_titles_release.entry_id=exp_channel_data_release.entry_id
INNER JOIN exp_channel_data exp_channel_data_order ON exp_channel_data_item.field_id_177=exp_channel_data_order.entry_id
INNER JOIN exp_channel_titles exp_channel_titles_order ON exp_channel_data_item.entry_id=exp_channel_titles_order.entry_id
WHERE exp_channel_titles_item.status='closed'
AND exp_channel_titles_item.entry_date>={embed:em_from_date}
AND exp_channel_titles_item.entry_date<={embed:em_to_date} + interval 1 day
{if embed:em_diamond_id != ''}
AND exp_channel_data_release.field_id_5={embed:em_diamond_id}
{/if}
ORDER BY exp_channel_titles_release.title ASC
"}
....
{/exp:query}