If all you want is the current week then the query is simple:
SELECT * FROM orders where WEEK(orders.order_date) = WEEK(NOW())
The default is thje day starts on Sunday so the second value is not needed.
WEEK(NOW(),7) is equivalent to WEEK(NOW(),0) - the valid values are 0 - 6.
As for performance, I tested the query against a table I have with 199,826
rows - it returned the data in 0.016 seconds (selecting distinct week(date))
and selecting * returned 3,816 rows in 0.827 seconds.
One concern will be when the data spans years - in that case you will need
to also check for year:
SELECT * FROM orders where WEEK(orders.date) = WEEK(NOW()) and
YEAR(orders.order_date) = YEAR(NOW())
----- Original Message -----
From: "ML" <mailinglists@stripped>
Sent: Monday, December 28, 2009 5:14 PM
> Hi All,
> trying to write some SQL that will give me records for the CURRENT WEEK.
> Example, starting on a Sunday and going through Saturday.
> This week it would be Dec 27 - Jan 2.
> I am doing this so I can write a query that will show orders that are
> placed during the current week.
> Here is what I have, but this is showing from today for the next seven
> SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7)
> AND DATEDIFF(NOW(),orders.order_date) < 7;
> Would anyone have any advice?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1