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.=20

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) =3D 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Weeks

See:
http://gtowey.blogspot.com/2009/04/how-to-select-this-wednes day-or-other.ht=
ml

just calculate the two dates, and use WHERE order_date BETWEEN (calculated =
start date) AND (calculated end date)

This avoids using functions on the actual column when possible, since that =
will prevent using indexes to find your query results.

Regards,
Gavin Towey

-----Original Message-----
From: ML [mailto:mailinglists@mailnewsrss.com]
Sent: Monday, December 28, 2009 4:15 PM
To: mysql@lists.mysql.com
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 place=
d 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) =3D 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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Weeks

--------------060901080104030007080607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

ML,

>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.

For the week of any date @d:

.... WHERE order_date BETWEEN AddDate(@d, -DayOfWeek(@d)+1) AND
AddDate(@d, 7-DayOfWeek(@d)) ...

PB

-----

ML wrote:
> 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
>
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.431 / Virus Database: 270.14.123/2592 - Release Date: 12/29/09 07:47:00
>
>

--------------060901080104030007080607--

Re: Weeks

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"
To:
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=mysql@dawiz.net
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org