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,

cms · Accepted Answer · 2009-08-26 13:35:57Z

3

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())

edited Aug 26, 2009 at 13:35

answered Aug 26, 2009 at 11:52

cms

5,9922 gold badges31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Svetlozar Angelov Over a year ago

MONTH(r.date) = 07 and Year(r.date) = 2009 can't be worse in performance than r.date BETWEEN concat(date_format(LAST_DAY(now() -interval 1 month),'%Y-%m-'),'01') AND last_day(NOW() - INTERVAL 1 MONTH)

cms Over a year ago

perhaps not, but the use between operator, against a constant uper bound might be more optimal for indexed selection

codecowboy Over a year ago

thanks for the answers. So I would do month(now() - interval 1 month) for the previous month?

codecowboy · Accepted Answer · 2009-08-26 16:41:38Z

0

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

answered Aug 26, 2009 at 16:41

codecowboy

10.2k18 gold badges85 silver badges138 bronze badges

Collectives™ on Stack Overflow

MySql get rows between date values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related