List:General Discussion« Previous MessageNext Message »
From:DaWiz Date:December 29 2009 11:16pm
Subject:Re: Weeks
View as plain text  
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>
To: <mysql@stripped>
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=1
> 

Thread
WeeksML29 Dec
  • RE: WeeksGavin Towey29 Dec
  • Re: WeeksPeter Brawley29 Dec
  • Re: WeeksDaWiz30 Dec