Search on records for current week only

[n1concepts]

Can someone look at this query and tell me what I have wrong - it doesn't error but yield zero results when I know there are thousands of rows for this current Year (2013) and month (July).

mysql> select rid,cdate from records 
WHERE YEAR(cdate) = CURDATE() AND 
MONTH(cdate) = MONTH(CURDATE()) AND 
DAYOFWEEK(cdate) <=7;
Empty set

mysql> 

Note: I need to match on the current month (of this present year) and pull all records the present week (Sunday thru Saturday).

So for this moment (7/8/13) that would be anything match 7/6/13 thru 7/13/13 <when that time elapse of course)>.

 

Q: What am I doing wrong w/above query?

[DavidAM]

WHERE YEAR(cdate) = CURDATE() AND : This will never be true since YEAR() is an integer (2013) and CURDATE() is a DATE (2013-07-08).

 

If you have "thousands" of rows for a single date, then you probably should have the cdate column indexed. But the query you posted will NOT use the index, because you are performing calculations on the column. You will get better results using BETWEEN or IN

 

WHERE cdate BETWEEN '2013-07-06' AND '2013-07-13'

[n1concepts]

Yes, that column is indexed and I see your point about using BETWEEN or IN to take advantage of the indexing oppose to calculating which will result in full search.

That leads to next question: I need this WHERE clause to be dynamic to always pull Sunday thru Saturday for the current week (so based on current date).

 

So I still need something like:

WHERE MONTH(cdate) = CURDATE() AND 
(DAYOFWEEK(cdate) BETWEEN 1 AND 7)

I know this is wrong as it's not producing any results but using to make point - how do I go about:

1. grabbing the current date to extract the present year (as there are multiple years and i only need current month and year)

2. from the current month and year, I filter on just that week "starting with Sunday" and "ending with Saturday" for said week.

 

That's the issue I'm still trying to figure out... thx!

I do appreciate your insight as this one killing me...

[n1concepts]

I got it - thx for input!

WHERE YEAR(cdate) AND 
MONTH(cdate) = MONTH(NOW()) AND
(DAYOFWEEK(cdate) BETWEEN 1 AND 7)

[DavidAM]

I got it - thx for input!

WHERE YEAR(cdate) AND 
MONTH(cdate) = MONTH(NOW()) AND
(DAYOFWEEK(cdate) BETWEEN 1 AND 7)

 

That query will still not take advantage of the index. Plus, it will return all dates in the current month. This is not what you said you wanted. If you want only ONE WEEK, surrounding the current date, I would use a BETWEEN clause on the date and calculate the first and last date :

 

WHERE cdate BETWEEN DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) - 1) DAY)
             AND DATE_ADD(CURDATE(), INTERVAL (7 - DAYOFWEEK(CURDATE())) DAY)