Query module - can't filter by date range?

[eluser]Unknown[/eluser]
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'

Code:

{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}

[eluser]CroNiX[/eluser]
Dates in mysql are stored as yyyy-mm-dd. Are em_from_date and em_to_date in that format?

[eluser]CroNiX[/eluser]
Your interval might also need to be something like:

Code:

exp_channel_titles_item.entry_date<= ADDDATE(embed:em_to_date, INTERVAL 1 DAY)

[eluser]Unknown[/eluser]
[quote author="CroNiX" date="1386364251"]Dates in mysql are stored as yyyy-mm-dd. Are em_from_date and em_to_date in that format?[/quote]

You are correct, and I actually had forgotten that I already made that conversion elsewhere in the code. I got it working. I had to convert the date to a timestamp:

Code:

AND exp_channel_titles_order.entry_date >= UNIX_TIMESTAMP('{embed:em_from_date}')
  AND exp_channel_titles_order.entry_date <= UNIX_TIMESTAMP('{embed:em_to_date}' + interval 1 day)

I saw dozens of examples that worked for people without doing this, but not for me. ¯\_(ツ)_/¯