Filter query by CURDATE()

Question

SELECT *
FROM `summary`
WHERE submit_date = curdate()
AND submit_date > '06:00'
AND submit_date < '21:00'
LIMIT 0 , 30

This is pulling all dates including today? submit_date is a column that shows has DATETIME and it's not filtering them out like they should be. What am I missing? Thanks!

Answer 1

Your 2 limiting conditions should be comparing against the value returned by TIME() if it is a DATETIME column, and the first condition should be limited to the DATE() portion only.

SELECT *
FROM `summary`
WHERE
  /* Truncate to the date only to compare against CURDATE() */
  DATE(submit_date) = curdate()
  /* And truncate to the TIME() to compare against these time literals. */
  /* note also that the time literals may need seconds as well */
  AND TIME(submit_date) > '06:00:00'
  AND TIME(submit_date) < '21:00:00'
LIMIT 0 , 30