SEO

Hi Guys,

I'm kind of at a standstill in coming up with how to get a query I need to
write.
I'm trying to find a record which matches the week we are in.
Example today is Thursday the 29th, it is within the week which has the 26th
through the 1st, and if the day is within this week display the record that
has the date 3/26/2007.

Basically we are returning a special which is dated each Monday, any day
within that week should show the Monday value.

I appreciate your help!

Joey

I'm not sure if this gets you all the way, or not.

There is a WEEK() function that converts a date into its week of the year.
There isn't any obvious way to turn it back into a date, but it doesn't
sound like you need it for your particular application.

SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7)
AND DATEDIFF(specials.start_date,DATE(NOW)) < 7;

Since you have the start date for your special, then you will be getting it
back from your query anyways so you don't need to convert back. You can make
your queries more efficient if you store the week the special starts as well
as its date, saving one function call in your query and allowing you to
index on that week field.

The second part of the WHERE clause should keep you within the right year,
if I did it correctly.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Joey [mailto:Joey@Web56.Net]
> Sent: Thursday, March 29, 2007 9:46 AM
> To: MySQL DB
> Subject: Help With a Week ( date ) query
>
> Hi Guys,
>
> I'm kind of at a standstill in coming up with how to get a
> query I need to
> write.
> I'm trying to find a record which matches the week we are in.
> Example today is Thursday the 29th, it is within the week
> which has the 26th
> through the 1st, and if the day is within this week display
> the record that
> has the date 3/26/2007.
>
> Basically we are returning a special which is dated each
> Monday, any day
> within that week should show the Monday value.
>
> I appreciate your help!
>
> Joey
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>

Hi all. I just finished installing mysql on a new server. I noticed that
database information_schema wasn't found. Can someone help me.


********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

At 12:30 PM -0500 3/29/07, Brown, Charles wrote:
>Hi all. I just finished installing mysql on a new server. I noticed that
>database information_schema wasn't found. Can someone help me.

What version of MySQL? I_S is present only in 5.0 and up.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Jerry:

We do this exactly at:

http://www.WeeklyRentals.com

Jim

> I'm not sure if this gets you all the way, or not.
>
> There is a WEEK() function that converts a date into its week of the year.
> There isn't any obvious way to turn it back into a date, but it doesn't
> sound like you need it for your particular application.
>
> SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7)
> AND DATEDIFF(specials.start_date,DATE(NOW)) < 7;
>
> Since you have the start date for your special, then you will be getting
> it
> back from your query anyways so you don't need to convert back. You can
> make
> your queries more efficient if you store the week the special starts as
> well
> as its date, saving one function call in your query and allowing you to
> index on that week field.
>
> The second part of the WHERE clause should keep you within the right year,
> if I did it correctly.
>
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>> -----Original Message-----
>> From: Joey [mailto:Joey@Web56.Net]
>> Sent: Thursday, March 29, 2007 9:46 AM
>> To: MySQL DB
>> Subject: Help With a Week ( date ) query
>>
>> Hi Guys,
>>
>> I'm kind of at a standstill in coming up with how to get a
>> query I need to
>> write.
>> I'm trying to find a record which matches the week we are in.
>> Example today is Thursday the 29th, it is within the week
>> which has the 26th
>> through the 1st, and if the day is within this week display
>> the record that
>> has the date 3/26/2007.
>>
>> Basically we are returning a special which is dated each
>> Monday, any day
>> within that week should show the Monday value.
>>
>> I appreciate your help!
>>
>> Joey
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>>
>>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jim@oats.com
>
>

The first part of the WHERE clause gives you the week number of a record's
date, and compare it with today's week number. Note that
WEEK("2008-01-01",7) will return 53, indicating that because 2008-01-01 is a
Tuesday it is part of the last week of 2007. That, I think, is what you
want.

The second clause is supposed to make sure that we aren't finding records
that are in the same week but in previous years. In other words, if we are
in week 23 we don't want to find records that are from week 23 ten years
ago. I think I got the arguments to DATEDIFF backwards, though. The query
should read


SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7)
AND DATEDIFF(NOW(),specials.start_date) < 7;

Let me know if that works.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Joey [mailto:Joey@Web56.Net]
> Sent: Saturday, March 31, 2007 4:18 AM
> To: 'Jerry Schwartz'
> Subject: RE: Help With a Week ( date ) query
>
> Hi Jerry,
> This part of the query is working, but something in the AND
> part isn't and
> is failing.
>
> SELECT * FROM special WHERE WEEK(NOW(), 7) = WEEK(special.date, 7)
>
> AND DATEDIFF(specials. date,DATE(NOW()) < 7;
>
>
> Thanks for your help!
>
> -----Original Message-----
> From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
> Sent: Thursday, March 29, 2007 10:29 AM
> To: 'Joey'; 'MySQL DB'
> Subject: RE: Help With a Week ( date ) query
>
> I'm not sure if this gets you all the way, or not.
>
> There is a WEEK() function that converts a date into its week
> of the year.
> There isn't any obvious way to turn it back into a date, but
> it doesn't
> sound like you need it for your particular application.
>
> SELECT * FROM specials WHERE WEEK(NOW(), 7) =
> WEEK(specials.start_date, 7)
> AND DATEDIFF(specials.start_date,DATE(NOW)) < 7;
>
> Since you have the start date for your special, then you will
> be getting it
> back from your query anyways so you don't need to convert
> back. You can make
> your queries more efficient if you store the week the special
> starts as well
> as its date, saving one function call in your query and
> allowing you to
> index on that week field.
>
> The second part of the WHERE clause should keep you within
> the right year,
> if I did it correctly.
>
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
> > -----Original Message-----
> > From: Joey [mailto:Joey@Web56.Net]
> > Sent: Thursday, March 29, 2007 9:46 AM
> > To: MySQL DB
> > Subject: Help With a Week ( date ) query
> >
> > Hi Guys,
> >
> > I'm kind of at a standstill in coming up with how to get a
> > query I need to
> > write.
> > I'm trying to find a record which matches the week we are in.
> > Example today is Thursday the 29th, it is within the week
> > which has the 26th
> > through the 1st, and if the day is within this week display
> > the record that
> > has the date 3/26/2007.
> >
> > Basically we are returning a special which is dated each
> > Monday, any day
> > within that week should show the Monday value.
> >
> > I appreciate your help!
> >
> > Joey
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
> >
> >
>
>
>
>
>
>