MySql get rows between date values

Question

I'm doing the following query and getting all rows returned, regardless of date:

 SELECT DISTINCT p.name, p.category, u.f_name, t.name
 FROM   (
        prizes p, tags t, results r
        )
 LEFT JOIN
        users u
 ON     (r.user_id = u.id)
 LEFT JOIN
        f_tag_lookup tl
 ON     (tl.tag_id = t.id)
 WHERE  r.tag_id = t.id
        AND r.date BETWEEN concat(date_format(LAST_DAY(now() - interval 1 month), '%Y-%m-'),'01') AND last_day(NOW() - INTERVAL 1 MONTH)

r.date is a datetime field. there are three rows with dates from last month and three rows with dates from this month but I'm getting all rows back?

Would appreciate any pointers. I want to return results between the first and last day of last month i.e. all results for July.

thanks,

Answer 1

Could you not just use the month() date function ?

e.g. use month(date) = month(now()) as the filter.

You would need to also match on year(date) = year(now()), if you had data from more than one year.

I'm not sure of the performance, but it seems more readable to me to express it that way.

SELECT DISTINCT p.name, p.category, u.f_name, t.name
FROM (prizes p, tags t, results r)
LEFT JOIN users u ON (r.user_id = u.id)
LEFT JOIN f_tag_lookup tl ON (tl.tag_id = t.id)
WHERE r.tag_id = t.id AND 
      month(r.date) = month(now()) AND 
      year(r.date) = year(now())

Answer 2

figured it out. The SQL regarding dates was fine but the JOINS were wrong. Thanks for the replies.