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" <mailingli...@mailnewsrss.com>
To: <mysql@lists.mysql.com>
Sent: Monday, December 28, 2009 5:14 PM
Subject: Weeks
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
days.
SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7)
AND DATEDIFF(NOW(),orders.order_date) < 7;
Would anyone have any advice?
-Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@dawiz.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
