RE: Help With a Week ( date ) query
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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
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:[EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a Week ( date ) query
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:[EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
